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.