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

  • "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.

Comment

  • "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.

Children
No Data
Generic Content
Generic Content

*Community Hub is the new name for Sage City