How to Find Text by Start Characters in T-SQL

Problem:

You’d like to select rows that contain a string starting with one of a given group of characters.

Example:

Our database has a table named gamer with data in the id and user columns.

Let’s find all the data for gamers whose user names start with 'a', 'b', 'r', or 's'.

Solution:

SELECT id, 
  user
FROM gamer
WHERE user LIKE '[abrs]%';

Here is the result:

Discussion:

If you want to filter rows containing a string that matches a given pattern, use the WHERE clause with the LIKE operator. The WHERE clause is used to filter records that meet stated conditions. After WHERE, you add a column name or an expression, followed by the operator (e.g. LIKE) and the comparison value(s). After LIKE, you enter the pattern to match (in our example, WHERE user LIKE '[abrs]%'). The pattern can contain characters and wildcards.

One of T-SQL’s wildcards is '%', which represents zero or more unknown characters. The underscore wildcard (_) indicates one character. The brackets ([]) enclose the pattern to match; this pattern can contain one or more letters or numbers. The target string can match one or more of the characters in the brackets.

In our example, the pattern '[abrs]%' denotes that the user name has to start with either a, b, r, or s. After the starting character, there can be zero or more characters because we’ve used the % wildcard.

The users with id = 2 and id = 3 are not displayed; their names start with l and m and thus don’t match the pattern we’ve selected.