SQL Server Performance Forum – Threads Archive

Select using like

Hello,
How do I search for a string that contains a single quote? I tried to use escape with no luck. For example: select * from table99
where lastname like ‘%’%’ I need to find the appostrophe anywhere in the name. Thanks
J

select * from table99
where lastname like ‘%”%’ should work —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

select data from
(
select ‘test’ as data union all
select ‘test”s test’ as data union all
select ‘no test’ as data union all
select ‘seems to be test’ as data union all
select ‘test of test” test’ as data
) T where data like ‘%”%’
Madhivanan Failing to plan is Planning to fail

Frank, you are fast [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] Other method where charindex(””,data)>0 Madhivanan Failing to plan is Planning to fail

Or, to make it somewhat easier to read, WHERE CHARINDEX(CHAR(39),data)>0 or WHERE data like ‘%’ + CHAR(39) + ‘%’

<blockquote id="quote">quote:<hr height="1" noshade id="quote"> …somewhat easier to read… <hr height="1" noshade id="quote"></blockquote id="quote"> Hm, really…? [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>

Well, at least you don’t have to count the number of single quotes, remember how many you have to type anyway, worry about having typed a double-quote between single quotes (‘"’) … Ah, it’s all such great fun.[<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />]

You have a point here. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

]]>