% in The Beginning of Like Clause

% in the beginning of like clause

A % on the beginning of a LIKE clause means that indexes are completely useless. If there is static text to anchor the pattern to in front of the %, there's at least potential utility to be obtained from indexes.

Is there a combination of LIKE and IN in SQL?

There is no combination of LIKE & IN in SQL, much less in TSQL (SQL Server) or PLSQL (Oracle). Part of the reason for that is because Full Text Search (FTS) is the recommended alternative.

Both Oracle and SQL Server FTS implementations support the CONTAINS keyword, but the syntax is still slightly different:

Oracle:

WHERE CONTAINS(t.something, 'bla OR foo OR batz', 1) > 0

SQL Server:

WHERE CONTAINS(t.something, '"bla*" OR "foo*" OR "batz*"')

The column you are querying must be full-text indexed.

Reference:

• Building Full-Text Search Applications with Oracle Text
• Understanding SQL Server Full-Text

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, '%', '[%]') + '%'

Combining LIKE and IN for SQL Server

Effectively, the IN statement creates a series of OR statements... so

SELECT * FROM table WHERE column IN (1, 2, 3)

Is effectively

SELECT * FROM table WHERE column = 1 OR column = 2 OR column = 3

And sadly, that is the route you'll have to take with your LIKE statements

SELECT * FROM table
WHERE column LIKE 'Text%' OR column LIKE 'Hello%' OR column LIKE 'That%'

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.

LIKE clause vs. IN clause--which is better?

Here's a good reference:

http://msdn.microsoft.com/en-us/library/aa933232%28SQL.80%29.aspx

How to retrieve rows that begin and end with specific characters?

I think you are looking for something like:

SELECT ID, NAME FROM STUDENT WHERE NAME LIKE 'ab%k';

For wildcard operations you should use a WHERE clause with the LIKE keyword that allows you to filter columns that match a given pattern using the %symbol as a wildcard.

There is a question about the List of special characters for SQL LIKE clause that has a good list of LIKE special characters

MySQL collate clause - before or after the like clause?

Aslong as mysql doent impemted it and i don't think it is possible anyway

You can check with

SHOW COLLATION WHERE Charset = 'utf8mb4';

Which collations are supported

and at https://dev.mysql.com/doc/refman/8.0/en/charset-mysql.html you can see all have utf8mb4_ at the beginning.

If you want go deeper. you can start with https://dev.mysql.com/doc/refman/8.0/en/charset-charsets.html

 'Abc' collate utf8_general_ci    

That utf8_general_ci belongs to the string 'Abc' which has no uf8mb4 (4byte) characters and can be savely converted to that cllation.

name collate utf8_general_ci

That utf8_general_ci belongs to the column name which is uf8mb4 (4byte) and can not be converted ro that collation, because it isn't supported(see above)

how to use LIKE with column name

You're close.

The LIKE operator works with strings (CHAR, NVARCHAR, etc). so you need to concattenate the '%' symbol to the string...

MS SQL Server:

SELECT * FROM table1,table2 WHERE table1.x LIKE table2.y + '%'

Use of LIKE, however, is often slower than other operations. It's useful, powerful, flexible, but has performance considerations. I'll leave those for another topic though :)

EDIT:

I don't use MySQL, but this may work...

SELECT * FROM table1,table2 WHERE table1.x LIKE CONCAT(table2.y, '%')

---

Related Topics