Querying searching Pattern Ranges and Wild Cards in sqlserver

 

Querying Pattern Ranges

The % wildcard character represents any number of characters of any length. Let’s find all first names that end in the letter ‘A’. By using the percentage ‘%’ sign with the letter ‘A’, we achieve this goal using the code sample below:

SELECT *
FROM Employee
WHERE FirstName LIKE '%A'

To find all FirstName values beginning with the letters ‘A’ or ‘B’ we can use two predicates in our WHERE clause, by separating them with the OR statement.

34594fbf54 j2pcontest 613 1

Finding names beginning with an ‘A’ or ‘B’ is easy and this works fine until we want a larger range of letters as in the example below for ‘A’ thru ‘K’:

SELECT *
FROM Employee
WHERE FirstName LIKE 'A%'
OR FirstName LIKE 'B%'
OR FirstName LIKE 'C%'
OR FirstName LIKE 'D%'
OR FirstName LIKE 'E%'
OR FirstName LIKE 'F%'
OR FirstName LIKE 'G%'
OR FirstName LIKE 'H%'
OR FirstName LIKE 'I%'
OR FirstName LIKE 'J%'
OR FirstName LIKE 'K%'

The previous query does find FirstName values beginning with the letters ‘A’ thru ‘K’. However, when a query requires a large range of letters, the LIKE operator has an even better option. Since the first letter of the FirstName field can be ‘A’, ‘B’, ‘C’, ‘D’, ‘E’, ‘F’, ‘G’, ‘H’, ‘I’, ‘J’ or ‘K’, simply list all these choices inside a set of square brackets followed by the ‘%’ wildcard, as in the example below:

SELECT *
FROM Employee
WHERE FirstName LIKE '[ABCDEFGHIJK]%'

A more elegant example of this technique recognizes that all these letters are in a continuous range, so we really only need to list the first and last letter of the range inside the square brackets, followed by the ‘%’ wildcard allowing for any number of characters after the first letter in the range.

4451840f42 j2pcontest 613 2

 

 


Discover more from mycodetips

Subscribe to get the latest posts sent to your email.

Discover more from mycodetips

Subscribe now to keep reading and get access to the full archive.

Continue reading

Scroll to Top