CODEWITHSUNDEEP
sql
Mark
Mark

Reputation: 11

SQL Searching for results with apostrophes

I am trying to automate a problem at work and have an SQL question. I am getting a list from one of our vendors strips all of the apostrophes out of the info.

So when I search for "oneil" and my database has "o'neil"

select * from db where name = "oneil"

I know how to fix that by hand, but how would I make it work when I don't know where the apostrophe is at?

I hope I explained that right.

Upvotes: 0

Views: 81

Answers (2)

Eray Balkanli
Eray Balkanli

Reputation: 7960

For SQL Server, use a temp table with apostrophes replaced:

select *,
       derived_name = REPLACE(t.name,'''','')
into #temp
from table t

Then you can do: select * from #temp where derived_name='oneil'

Note that the apostrophe is also the escape character in sql server.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520918

You could first strip the apostrophes from the name before doing the comparison:

SELECT *
FROM db
WHERE REPLACE(name, '''', '') = 'oneil';

Demo

Note that in most versions of SQL, a literal apostrophe is represented inside a string literal using two apostrophes doubled-up ''.

Upvotes: 1

Related Questions