Apostrophe in UPDATE SET Query

31 Aug 2006 8:53 PM

Pancho

Hello,

I am doing some data corrections and tried to run this:
UPDATE tablename
SET Provider = 'Burkers'
WHERE Provider = 'Burker's'

Got this error message:
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 's'.
Server: Msg 105, Level 15, State 1, Line 3
Unclosed quotation mark before the character string '
'.

I tried brackets and double quotes but then t-sql returned an invalid column
name msg. Is there a way to express the whole string Burker's without going
berzerk?
Thanks,
Pancho

31 Aug 2006 8:57 PM

SQL Menace

you have to double the tick

UPDATE tablename
SET Provider = 'Burkers'
WHERE Provider = 'Burker''s'

Denis the SQL Menace
http://sqlservercode.blogspot.com/

Pancho wrote:
Show quote

> Hello,
>
> I am doing some data corrections and tried to run this:
> UPDATE tablename
> SET Provider = 'Burkers'
> WHERE Provider = 'Burker's'
>
> Got this error message:
> Server: Msg 170, Level 15, State 1, Line 3
> Line 3: Incorrect syntax near 's'.
> Server: Msg 105, Level 15, State 1, Line 3
> Unclosed quotation mark before the character string '
> '.
>
> I tried brackets and double quotes but then t-sql returned an invalid column
> name msg. Is there a way to express the whole string Burker's without going
> berzerk?
> Thanks,
> Pancho

31 Aug 2006 9:16 PM

Arnie Rowland

Since SQL Server is using a single quote for a string delimiter, embedded single quotes in a string must be 'doubled up'.

Sometimes it is useful to consider the replace function. For example:

DECLARE @MyTable table
   (    RowID    int        IDENTITY
      , LastName varchar(20)
   )

INSERT INTO @MyTable VALUES ( 'Burker''s' )

SELECT
     RowID
   , LastName
FROM @MyTable

--Note: 2 single quotes translate to one single quote
--      and they are inside a pair of single quotes
--      like all strings are.

UPDATE @MyTable
   SET LastName = replace( LastName, '''', '' )
   WHERE LastName LIKE '%''%'

SELECT
     RowID
   , LastName
FROM @MyTable

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

Show quote

"Pancho" <Pan***@discussions.microsoft.com> wrote in message news:4B541320-8656-4244-8CEA-3915BB927006@microsoft.com...
> Hello,
>
> I am doing some data corrections and tried to run this:
> UPDATE tablename
> SET Provider = 'Burkers'
> WHERE Provider = 'Burker's'
>
> Got this error message:
> Server: Msg 170, Level 15, State 1, Line 3
> Line 3: Incorrect syntax near 's'.
> Server: Msg 105, Level 15, State 1, Line 3
> Unclosed quotation mark before the character string '
> '.
>
> I tried brackets and double quotes but then t-sql returned an invalid column
> name msg. Is there a way to express the whole string Burker's without going
> berzerk?
> Thanks,
> Pancho

31 Aug 2006 9:27 PM

Pancho

Thank you SQL Menace and thank you Arnie Rowland. These approaches worked,
and I appreciate the promptitude. Best Regards, P

Show quote

"Arnie Rowland" wrote:

> Since SQL Server is using a single quote for a string delimiter, embedded single quotes in a string must be 'doubled up'.
>
> Sometimes it is useful to consider the replace function. For example:
>
>
> DECLARE @MyTable table
>    (    RowID    int        IDENTITY
>       , LastName varchar(20)
>    )
>
> INSERT INTO @MyTable VALUES ( 'Burker''s' )
>
> SELECT
>      RowID
>    , LastName
> FROM @MyTable
>
> --Note: 2 single quotes translate to one single quote
> --      and they are inside a pair of single quotes
> --      like all strings are.
>
> UPDATE @MyTable
>    SET LastName = replace( LastName, '''', '' )
>    WHERE LastName LIKE '%''%'
>
> SELECT
>      RowID
>    , LastName
> FROM @MyTable
>
>
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
>
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
>
> "Pancho" <Pan***@discussions.microsoft.com> wrote in message news:4B541320-8656-4244-8CEA-3915BB927006@microsoft.com...
> > Hello,
> >
> > I am doing some data corrections and tried to run this:
> > UPDATE tablename
> > SET Provider = 'Burkers'
> > WHERE Provider = 'Burker's'
> >
> > Got this error message:
> > Server: Msg 170, Level 15, State 1, Line 3
> > Line 3: Incorrect syntax near 's'.
> > Server: Msg 105, Level 15, State 1, Line 3
> > Unclosed quotation mark before the character string '
> > '.
> >
> > I tried brackets and double quotes but then t-sql returned an invalid column
> > name msg. Is there a way to express the whole string Burker's without going
> > berzerk?
> > Thanks,
> > Pancho