CODEWITHSUNDEEP
sql-server-2008replaceadventureworks
Zerotoinfinity
Zerotoinfinity

Reputation: 6540

How to replace ' from the select query

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?

Upvotes: 2

Views: 5504

Answers (1)

javram
javram

Reputation: 2645

How about:

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

Upvotes: 3

Related Questions