SQL 'Like' Query Using '%' Where the Search Criteria Contains '%'

SQL 'LIKE' query using '%' where the search criteria contains '%'

If you want a % symbol in search_criteria to be treated as a literal character rather than as a wildcard, escape it to [%]

... where name like '%' + replace(search_criteria, '%', '[%]') + '%'

How to write the LIKE syntax where the search term includes a percentage (%)?

A single character class represents a single character. So [%] means a literal percent symbol, and 8[%] means literal 8%. Try this:

SELECT * FROM yourTable WHERE [Notes] like '%8[%]%'

Demo

SQL SELECT WHERE field contains words

Rather slow, but working method to include any of words:

SELECT * FROM mytable
WHERE column1 LIKE '%word1%'
   OR column1 LIKE '%word2%'
   OR column1 LIKE '%word3%'

If you need all words to be present, use this:

SELECT * FROM mytable
WHERE column1 LIKE '%word1%'
  AND column1 LIKE '%word2%'
  AND column1 LIKE '%word3%'

If you want something faster, you need to look into full text search, and this is very specific for each database type.

How do I find ' % ' with the LIKE operator in SQL Server?

I would use

WHERE columnName LIKE '%[%]%'

SQL Server stores string summary statistics for use in estimating the number of rows that will match a LIKE clause. The cardinality estimates can be better and lead to a more appropriate plan when the square bracket syntax is used.

The response to this Connect Item states

We do not have support for precise cardinality estimation in the
presence of user defined escape characters. So we probably get a poor
estimate and a poor plan. We'll consider addressing this issue in a
future release.

An example

CREATE TABLE T
(
X VARCHAR(50),
Y CHAR(2000) NULL
)

CREATE NONCLUSTERED INDEX IX ON T(X)

INSERT INTO T (X)
SELECT TOP (5) '10% off'
FROM master..spt_values
UNION ALL
SELECT  TOP (100000)  'blah'
FROM master..spt_values v1,  master..spt_values v2

SET STATISTICS IO ON;
SELECT *
FROM T 
WHERE X LIKE '%[%]%'

SELECT *
FROM T
WHERE X LIKE '%\%%' ESCAPE '\'

Shows 457 logical reads for the first query and 33,335 for the second.

SQL like search string starts with

SELECT * from games WHERE (lower(title) LIKE 'age of empires III');

The above query doesn't return any rows because you're looking for 'age of empires III' exact string which doesn't exists in any rows.

So in order to match with this string with different string which has 'age of empires' as substring you need to use '%your string goes here%'

More on mysql string comparision

You need to try this

SELECT * from games WHERE (lower(title) LIKE '%age of empires III%');

In Like '%age of empires III%' this will search for any matching substring in your rows, and it will show in results.

Why does using an Underscore character in a LIKE filter give me all the results?

Modify your WHERE condition like this:

WHERE mycolumn LIKE '%\_%' ESCAPE '\'

This is one of the ways in which Oracle supports escape characters. Here you define the escape character with the escape keyword. For details see this link on Oracle Docs.

The '_' and '%' are wildcards in a LIKE operated statement in SQL.

The _ character looks for a presence of (any) one single character. If you search by columnName LIKE '_abc', it will give you result with rows having 'aabc', 'xabc', '1abc', '#abc' but NOT 'abc', 'abcc', 'xabcd' and so on.

The '%' character is used for matching 0 or more number of characters. That means, if you search by columnName LIKE '%abc', it will give you result with having 'abc', 'aabc', 'xyzabc' and so on, but no 'xyzabcd', 'xabcdd' and any other string that does not end with 'abc'.

In your case you have searched by '%_%'. This will give all the rows with that column having one or more characters, that means any characters, as its value. This is why you are getting all the rows even though there is no _ in your column values.

SQL to Query text in access with an apostrophe in it

You escape ' by doubling it, so:

Select * from tblStudents where name like 'Daniel O''Neal' 

Note that if you're accepting "Daniel O'Neal" from user input, the broken quotation is a serious security issue. You should always sanitize the string or use parametrized queries.

How to write a SQL query to find values which contain '%' symbol?

You could just use string functions:

select val from mytable where locate(val, '%')

If you want to do this with like, you need the escape option:

select val from mytable where val like '%|%%' escape '|';

---

Related Topics