CODEWITHSUNDEEP
mysqlsqlselectwildcardsql-like
Eldon Hipolito
Eldon Hipolito

Reputation: 724

How does % wildcard exactly work in sql when searching

I saw a select statement with something like this in the where part 'LIKE %Blabla%Another%', now I tried it myself and I don't clearly understand how it works. The thing I'm confused about is the % in the middle, I understand how '%Blabla Another%' works, but with the % as a replacement for the space, I got confused.

Upvotes: 1

Views: 1870

Answers (5)

user5524279
user5524279

Reputation:

Here is a listing of the wildcards that you can use and below some examples.

  • %: A substitute for zero or more characters
  • _: A substitute for a single character
  • [charlist]: Sets and ranges of characters to match
  • [!charlist]: Matches only a character NOT specified within the brackets

The following SQL statement selects all customers with a City containing the pattern "es"

  • SELECT * FROM Customers WHERE City LIKE '%es%'

The following SQL statement selects all customers with a City starting with any character, followed by "erlin":

  • SELECT * FROM Customers WHERE City LIKE '_erlin'

The following SQL statement selects all customers with a City starting with "b", "s", or "p":

  • SELECT * FROM Customers WHERE City LIKE '[bsp]%'

The following SQL statement selects all customers with a City NOT starting with "b", "s", or "p":

  • SELECT * FROM Customers WHERE City LIKE '[!bsp]%'

Source: http://www.w3schools.com/sql/sql_wildcards.asp

Upvotes: 0

Al Ameen
Al Ameen

Reputation: 372

In SQL, wildcard characters are used with the SQL LIKE operator.

there are the wild cards in sql %,_,[charlist],[^charlist].

the % wild cards is a substitute for zero or more characters.

for example 

 SELECT * FROM Customers
    WHERE City LIKE '%es%';

this will selects all customers with a City containing the pattern "es".

so here you can see that the % wild card act as null or space character.

Upvotes: 0

vladkras
vladkras

Reputation: 17228

With LIKE you can use the following two wildcard characters in the pattern:

% matches any number of characters, even zero characters.

_ matches exactly one character.

© dev.mysql.com

Upvotes: 0

Sven Tore
Sven Tore

Reputation: 987

% means any (including none) chars.

If you have a string 'abcdef' it would match e.g 'a%f' since the string start and ends with a and f.

'%b%e%'would also match as it means any string with b and even, in that order, but not necessarily next to each other

Upvotes: 0

Mureinik
Mureinik

Reputation: 311163

% means "any sequence of characters, including an empty one". So LIKE '%Blabla%Another%' will match, for example 'XYZBlablaABCAnotherPQR', 'BlablaAnother' and ' Blabla Another '

Upvotes: 2

Related Questions