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

  • Thank you for reminding me of URL encoding. For some reason, working server to server tricked me into thinking that's not something to consider.

    However,

    I'd like clarify that my requests with the following were returning the entire set of entities instead of the expected subset.

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

    The above was returning all entries for Entity instead of just the entities for a company with the attribute containing the word "case".

    It has obviously been a while since I asked about this and since then I've employed a workaround.

    Thank you for the help.

Comment

  • Thank you for reminding me of URL encoding. For some reason, working server to server tricked me into thinking that's not something to consider.

    However,

    I'd like clarify that my requests with the following were returning the entire set of entities instead of the expected subset.

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

    The above was returning all entries for Entity instead of just the entities for a company with the attribute containing the word "case".

    It has obviously been a while since I asked about this and since then I've employed a workaround.

    Thank you for the help.

Children
No Data
Generic Content
Generic Content

*Community Hub is the new name for Sage City