x Want to participate? Join the group to interact
Sage CRM
Related
Recommended

Finding %, _ and other Special Characters in SQL

Less than one minute read time.

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 '\'

Parents

  • 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'

Comment

  • 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'

Children
No Data
Generic Content
Generic Content

*Community Hub is the new name for Sage City