CODEWITHSUNDEEP
sql
Bit Coins420
Bit Coins420

Reputation: 13

Processing apostrophe in an update

I have a situation where I have to manually normalize some data. The problem is the data has an actual single quote in it. I was hoping I have some options other than data entry...

One of my fields has data that I want to update. it has this date > Women's Health ServicesOBGYN3

I want to update it to 'OBGYN' using the update feature. If using double quotes: 

update [Programmer].[dbo].[RevenueDetail$] set Dept = 'OBGYN' where Dept = "Women's Health ServicesOBGYN3"

I get this error. Msg 207, Level 16, State 1, Line 1 Invalid column name 'Women's Health ServicesOBGYN3'.

If using single quotes:

update [Programmer].[dbo].[RevenueDetail$] set Dept = 'OBGYN' where Dept = 'Women's Health ServicesOBGYN3'

Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 's'. Msg 105, Level 15, State 1, Line 1 Unclosed quotation mark after the character string ''.

Upvotes: 1

Views: 127

Answers (1)

SqlZim
SqlZim

Reputation: 38033

Use two single quotes for each single quote in the string. e.g.

select 'Women''s Health ServicesOBGYN3'

So your update would look like:

update [Programmer].[dbo].[RevenueDetail$] set Dept = 'OBGYN' where Dept = 'Women''s Health ServicesOBGYN3'

Upvotes: 1

Related Questions