SQL Server Performance

Select using like

Discussion in 'T-SQL Performance Tuning for Developers' started by loiter99, Feb 16, 2006.

  1. loiter99 New Member

    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
  2. FrankKalis Moderator

  3. Madhivanan Moderator

    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
  4. Madhivanan Moderator

    Frank, you are fast [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />Other method<br /><br />where charindex('''',data)&gt;0<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  5. Adriaan New Member

    Or, to make it somewhat easier to read,

    WHERE CHARINDEX(CHAR(39),data)>0

    or

    WHERE data like '%' + CHAR(39) + '%'
  6. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />...somewhat easier to read...<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Hm, really...? [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />
  7. Adriaan New Member

    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=':p' />]
  8. FrankKalis Moderator

    You have a point here. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />

Share This Page