LIKE contains %

I have r开发者_如何学JAVAecord in table like 'abc 100% text'.

I want to search all the records that contain 100%.

What will be LIKE query?

SELECT * FROM TABLE where ColName LIKE '100%%'

above query returns wrong results.

Thanks.

SELECT * FROM TABLE where ColName LIKE '%100[%]%'

Have a look at Using Wildcard Characters As Literals

You can use the wildcard pattern matching characters as literal characters. To use a wildcard character as a literal character, enclose the wildcard character in brackets.

SELECT columns FROM table WHERE column LIKE '%[%]%'

or

SELECT columns FROM table WHERE column LIKE '%\%%' ESCAPE '\'

as described in http://web.archive.org/web/20150519072547/http://sqlserver2000.databases.aspfaq.com:80/how-do-i-search-for-special-characters-e-g-in-sql-server.html

Additionally, you can use escape charaters...

LIKE '%100^%%' ESCAPE '^'

http://web.archive.org/web/20150519072547/http://sqlserver2000.databases.aspfaq.com:80/how-do-i-search-for-special-characters-e-g-in-sql-server.html

Use a backslash to escape:

SELECT * FROM TABLE where ColName LIKE '%100\%%';

You may need to try this:

SELECT * FROM TABLE where ColName LIKE '%100\%%' ESCAPE '\';