CODEWITHSUNDEEP
mysqlsqlstringwhere-clausesql-like
Anubhav Kr. Srivastava
Anubhav Kr. Srivastava

Reputation: 63

Types of Wildcards in MySql

My query:

Select * From tableName Where columnName Like "[PST]%"

is not giving the expected result.

Why does this wildcard not work in MySql?

Upvotes: 5

Views: 4452

Answers (3)

Anubhav Kr. Srivastava
Anubhav Kr. Srivastava

Reputation: 63

Thanks everyone!

For specific this question, we need to use regexp

Select * From tableName Where ColumnName Regexp "^[PST]";

For more detail over Regular Expression i.e Regexp :

https://www.youtube.com/watch?v=KoltE-JUY0c

Upvotes: -3

Bill Karwin
Bill Karwin

Reputation: 562358

MySQL's LIKE syntax is documented here: https://dev.mysql.com/doc/refman/8.0/en/pattern-matching.html

Standard SQL from decades ago defined only two wildcards: % and _. These are the only wildcards an SQL product needs to support if they want to say they are SQL compliant and support the LIKE predicate.

% matches zero or more of any characters. It's analogous to .* in regular expressions.

_ matches exactly one of any character. It's analogous to . in regular expressions.

Also if you want to match a literal '%' or '_', you need to escape it, i.e. put a backslash before it:

WHERE title LIKE 'The 7\% Solution'

Microsoft SQL Server's LIKE syntax is documented here: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql?view=sql-server-ver15

They support % and _ wildcards, and the \ escape character, but they extend standard SQL with two other forms:

[a-z] matches one character, but only characters in the range inside the brackets. This is similar in regular expressions. The - is a range operator, unless it appears at the start or end of the string inside the brackets.

[^a-z] matches one character, which must not be one of the characters in the range inside the brackets. Also the same in regular expressions.

These are not standard forms of wildcards for the LIKE predicate, and other brands of SQL database don't support them.

Later versions of the SQL standard introduced a new predicate SIMILAR TO which supports much richer patterns and wildcards, since the right-side operand is a string which contains a regular expression. But since this predicate was introduced in a later edition of the SQL standard, some implementations had already developed their own solution that was almost the same.

MySQL called the operator REGEXP and RLIKE is a synonym (https://dev.mysql.com/doc/refman/8.0/en/regexp.html).

It was requested in https://bugs.mysql.com/bug.php?id=746 to support SIMILAR TO syntax to help MySQL comply with the SQL standard, but the request was turned down, because it had subtly different behavior to the existing REGEXP/RLIKE operator.

Microsoft SQL Server has partial support of regular expression wildcards in the LIKE operator, and also a dbo.RegexMatch() function.

SQLite has a GLOB operator, and so on.

Upvotes: 5

GMB
GMB

Reputation: 222482

If you want to filter on strings that contain any 'P', 'S', or 'T', then you can use a regex:

where col rlike '[PST]'

If you want strings that contain substring 'PST', then no need for square brackets - and like is enough:

where col like '%PST%'

If you want the matching character(s) at the start of the string, then the regex solution looks like:

where col rlike '^PST'

And the like option would be:

where col like 'PST%'

Upvotes: 9

Related Questions