Articles Cookbook
Back to list
SQL Server

How to Replace Part of a String in T-SQL

Database:

Operators:

LIKE, WHERE

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.

iduser
1superman
2magnus
3lucy
4stan
5ali

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:

iduser
1superman
4stan
5ali

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.

Recommended courses:

Recommended articles:

See also:

go to top