How to replace ' from the select query

Question

In SQL Server, I have this query

SELECT DISTINCT  City FROM 
Person.Address

But this gives me two cities which has name like Ville De'anjou (i.e. ')

you can try the same by this

SELECT DISTINCT  City FROM 
Person.Address
WHERE City like '%''%'

Now, I am creating a SSIS package which is creating folder for each city. But the package fails at these two cities and throw this error

[ADO NET Source [1]] Error: An error occurred executing the provided SQL command: "SELECT AddressID, AddressLine1,PostalCode, City
 FROM Person.Address WITH(NOLOCK)
 WHERE City = 'Ville De'anjou'". Incorrect syntax near 'anjou'.
Unclosed quotation mark after the character string ' SET FMTONLY OFF;'.

It fails because this isn't a correct query.

I have tried with this but no luck, instead it removed those two cities from the list

SELECT DISTINCT CITY FROM Person.Address
WHERE City like REPLACE(City,'''', '''''')

How can I rectify this error?

So you're creating a query by concatenation?

GSerg
– GSerg

2012-03-20 19:15:19 +00:00
Commented Mar 20, 2012 at 19:15 — GSerg
– GSerg, Commented Mar 20, 2012 at 19:15

javram · Accepted Answer · 2012-03-20 19:14:05Z

3

How about:

SELECT DISTINCT REPLACE(City,'''', '''''') FROM Person.Address
WHERE City like '%''%'

answered Mar 20, 2012 at 19:14

javram

2,6451 gold badge15 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Zerotoinfinity Over a year ago

that works when I hard coded the city name. but i want that to be replaced at the time of select only. I.e. SELECT DISTINCT City FROM Person.Address

Collectives™ on Stack Overflow

How to replace ' from the select query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related