Using Regular Expressions to Find Special Characters with T-SQL

By: Tim Smith

Overview

In the last section, we looked at applying regular expressions with numbers and included some special characters, such as the hyphen for negative numbers and the period for finding decimal precision points.  In this section, we’ll look at finding other special characters, introduce the “not” character, which can be useful for alphabetic and numerical character expressions, and look at applications with regular expressions.

Using Regex to Find Special Characters

We’ll use our existing tables to find special characters staring with our alphareg table.  In the first two queries, we look for any data row with one special character of an exclamation point [!] and in the next query we look for any special character of an exclamation point in any data row anywhere.

SELECT *
FROM alphareg
WHERE Alphabetic LIKE '[!]'
 
SELECT *
FROM alphareg
WHERE Alphabetic LIKE '%[!]%'   

No results from first query; one result from the second query.

Using Regex to Find Special Characters

Let’s expand our query further: suppose that we want to get all the data rows that have punctuation characters in them staring with the most common of comma, period, exclamation point, question mark, semicolon and colon.  In the below query, we look for each of these characters and get thirteen results. This will return any occurrence of these characters.

SELECT *
FROM alphareg
WHERE Alphabetic LIKE '%[,.!?;;]%'   

Thirteen results from this query.

In the above query, we’re looking for sentences with any of those special characters anywhere. 

Another Example Using Regex to Find Special Characters

What if we wanted to see how many sentences had a special character then a letter right after it (no spaces)?  Like we can do with combinations involving alphabetic or numerical characters, we can use combinations with special characters too.  In addition, we can also check if we have a special punctuation character, followed by a space, followed by any character, followed by an alphabetic character.

SELECT *
FROM alphareg
WHERE Alphabetic LIKE '%[,.!?;;][A-Z]%'
 
SELECT *
FROM alphareg
WHERE Alphabetic LIKE '%[,.!?;;][ ]%[A-Z]%'   

No results from the first query and six results from the second query.

We have no data that matches our first query from the above queries as every punctuation point is followed by either a space or ends the data row.  In our next query, we get six results – a special punctuation character being followed by a space, then anything, with an alphabetic character being next.

In these examples, we looked at some special characters.  In the next section, we’ll introduce the not symbol and how we can apply this with all special characters, along with applying it with numbers and alphabetic characters as well.

Additional Information

• Do any sentences have the special character of “ in our alphareg table and how would you write that query, based on what we’ve covered?