Introduction to Numeric Regular Expressions for T-SQL

By: Tim Smith

Overview

In the first part, we looked at regular expressions with alphabetic characters. Now, we'll look at regular expressions with numbers. Similar to regular expressions with alphabetic characters, we can specify exact lengths, ranges, and require certain precision lengths. We'll start off by creating a table that accepts numeric and varchar data across multiple columns. In this section, we'll also look at regular expressions with numbers within alphabetic column definitions, such as VARCHAR or CHAR.

Populate Test Numerical Data

We’ll create a table with three columns: decimal(15,4), smallint and varchar(25). We’ll run an initial query against each of these columns to see which rows begin with a numerical range of 2-5 [2-5]. Similar to what we learned with alphabetic characters, we can specify a range within the brackets, or if we want specific numbers like 1, 3 and 9, we can put those within a bracket without a range.

CREATE TABLE alphanumreg(
   NumData DECIMAL(15,4),
   NumInt SMALLINT,
   AlphabeticNum VARCHAR(25)
)
 
INSERT INTO alphanumreg
VALUES (22,22,'22')
   , (21.4,21,'21.4')
   , (40.05,40,'40.05')
   , (67,67,'67')
   , (1,1,'1')
   , (1.00,1,'1.00')
   , (121.23,121,'e1213')
   , (33.2341,33,'33.2341')
   , (33.2341,33,'33.2341')
   , (-1.09,-1,'-1.09')
   , (NULL,NULL,'22-E1-9')
   , (NULL,NULL,'11-EA-0')
   , (NULL,NULL,'04-E2-9')
   , (NULL,NULL,'10-E1-7')
   , (NULL,NULL,'106-E1-700')
   , (NULL,NULL,'3-E6-9365')
   , (NULL,NULL,'31-A2-4')
   , (NULL,NULL,'3723812695735285')
   , (NULL,NULL,'IX7254017')
   , (NULL,NULL,'4019638561283650')
GO

SELECT * 
FROM alphanumreg
WHERE NumData LIKE '[2-5]%'

SELECT * 
FROM alphanumreg
WHERE NumInt LIKE '[2-5]%'

SELECT * 
FROM alphanumreg
WHERE AlphabeticNum LIKE '[2-5]%'

Results from first three queries.

Use Regex to Find Text Rows Starting with a Number

Before we query this new table further, let’s look at our alphareg table to see if any rows start with any range of numbers 0 through 9. The below query shows us how we can identify any range of numbers, as one numerical character can only be in a range of 0 through 9 if we want to keep ourselves open to any possibility:

SELECT *
FROM alphareg
WHERE Alphabetic LIKE '[0-9]%'

Two results from the second query.

Use Regex to Find Text Rows that Contain a Number

If we check for a data row that has a numerical character of any range anywhere in the table alphareg, we get one more record:

SELECT *
FROM alphareg
WHERE Alphabetic LIKE '%[0-9]%'

Three results from the third query.

Use Regex to Find a Number Pattern

Similar to alphabetic characters, we can use the LIKE in our WHERE clause by allowing any character that precedes what numerical value or range we’re seeking or follows. We can also require an exact precision value, such as requiring that our number be only two digits. We’ll return to our alphanumreg table and query each column looking for values that only have two digits:

SELECT *
FROM alphanumreg
WHERE NumData LIKE '[0-9][0-9]'
 
SELECT *
FROM alphanumreg
WHERE NumInt LIKE '[0-9][0-9]'
 
SELECT *
FROM alphanumreg
WHERE AlphabeticNum LIKE '[0-9][0-9]'

Results from queries.

None of the decimal data fit this requirement, as all values exceed two digits in the column. Within the integers, we have six values that are only two digits and within the varchar, we have two values that are only two digits. Similar to the examples we saw with alphabetic data, we can specify a numerical precision of any number of characters.

Additional Information

How many rows of the decimal data start with either a two or a three?

Introduction to Numeric Regular Expressions for T-SQL

MENU