Using Regular Expressions to Find Special Characters with T-SQL




By:
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 '%[!]%'   
regex query results

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 '%[,.!?;;]%'   
regex query results

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]%'   
regex query results

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?

Last Update: 4/6/2018




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download





get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools