0
998
Question Hieu Dien Nguyen · Nov 29, 2018

Special Char % in SQL Query

#Caché #SQL

Hello everyone,

I try excuting a SQL Query like this SELECT ID, CompanyName FROM Company WHERE CompanyName LIKE 'condition' 

But if I want to search CompanyName have special char %, ex: '100% Co' , 'Group of Mr.%', 'The %TaxProp'...

How can I take % to query, with  

SELECT ID, CompanyName FROM Table1 WHERE CompanyName LIKE '%%%' 

it'll get all record in table.

Someone give me an idea. Thanks so much!

Discussion (5)0
Log in or sign up to continue

Comments

Brian Shingle  Nov 13, 2019 to Alexander Koblov

What if the character attempting to be escaped is a single quote?

0
Eduard Lebedyuk · Nov 29, 2018

Check LIKE documentation:

ESCAPE Clause
ESCAPE permits the use of a wildcard character as a literal character within pattern. ESCAPE char, if provided and if it is a single character, indicates that any character directly following it in pattern is to be understood as a literal character, rather than a wildcard or formatting character. The following example shows the use of ESCAPE to return values that contain the string '_SYS':

SELECT * FROM MyTable
WHERE symbol_field LIKE '%\_SYS%' ESCAPE '\'

So in your case:

SELECT ID, CompanyName
FROM Table1
WHERE CompanyName LIKE '%\%%' ESCAPE '\'
0
Hieu Dien Nguyen · Nov 29, 2018

That's best answer. Thanks so much, ESCAPE clause is very helpful yes

0