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 5
  • 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?

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

    I am aware of that. Thank you for confirming.

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

    I am trying to retrieve all entities which belong to a company and have a text attribute which contains the word "case" anywhere in it. For this purpose I've used the following query as per my initial comment.

    `where=Entity_companyId eq '11' and Entity_attribute LIKE '%\case'`

    However, that query simply returns all entities, regardless of their owning company.

  • Unguul

    If you are using SDATA or SDATA .2.0 then you need to be aware the error message that is returned along with the '500' code.  This is likely to be something like

    "URI: Malformed escape pair at index 113"

    This exception occurs if a page contains a URI with % character in the querystring and this is not a valid escape sequence (% followed by 2 characters representing an hexadecimal number 0-9 or a-f or A-F)

    See:  www.w3schools.com/.../ref_urlencode.asp

    The encoding for the % symbol is %25

    So we can write the request as

    where=comp_name like '%25C%25'