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

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

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

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

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

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

Generic Content
Generic Content

*Community Hub is the new name for Sage City