Finding %, _ and other Special Characters in SQL

Hints, Tips and Tricks

Technical Hints Tips and Tricks that cover customization and development using Sage CRM. API usage and coding are covered.

Finding %, _ and other Special Characters in SQL

  • Comments 3
  • Likes

I have just spent a frustrating few minutes trying to quickly find where '%' codes are used in translations.  Of course '%' is a special wild card character in SQL.  The same is true for '_' and other characters.

Don't do:

select capt_code, capt_family
from custom_captions
where capt_code like '%%%'

This gives nonsense results.

But either of these will work to find the data

select capt_code, capt_family
from custom_captions
where capt_code like '%[%]%'

OR
 
select capt_code, capt_family
from custom_captions
where capt_code like '%\%%' escape '\'

Comments
  • Great post, Jeff.  You can also use CHARINDEX() to do this in TSQL:

    SELECT 1 WHERE charindex('%', 'ABC') > 0

    SELECT 1 WHERE charindex('%', 'A%C') > 0

    The first SELECT won't return anything, because '%' isn't in 'ABC', but the second SELECT will return 1.  There's no need to escape the '%' if you use CHARINDEX().  By contrast, PATINDEX() does treat the '%' symbols as wildcard characters, so you can't use it quite as easily.

  • How do you query for strings starting with "c"?

    e.g. `where=Entity_companyId eq '11' and Entity_attribute LIKE '%case'`

    I'm aware `%c` has a special meaning in C so the response to that query replaces "%c" with some odd character but escaping it doesn't really make it better.

    e.g. `where=Entity_companyId eq '11' and Entity_attribute LIKE '%\case'` returns all entries, ignoring the company ID clause.

    Thank you.

  • Searching for strings that start with 'c' is not an issue in SQL.

    select * from company where

    comp_name like 'c%';

    Are you trying to search by passing in data into an SDATA request?