Extract the first number from an alphanumeric string in sql server

In this post, we are going to learn how to extract the first numeric value from an alphanumeric string in SQL Server. To demonstrate this, we will create a dummy table and then we will insert some dummy rows into that table. Finally, we will use a SELECT statement to extract the first numeric value from the given alphanumeric string for each row of the table.

Let’s create the dummy table as below:

--Create dummy table named testTable
IF(OBJECT_ID('dbo.testTable')) IS NOT NULL
DROP TABLE dbo.testTable
GO
CREATE TABLE dbo.testTable
(
val VARCHAR(100)
)
GO

Now, let’s insert some alphanumeric values in this table as below:

--Insert some dummy rows in testTable
INSERT INTO dbo.testTable
(val)
VALUES
('1846855445-DB-FC-D-B-FEDACAE'),
('912898124-B--EB-CBD-EEC'),
('AFAA---E-EDDEC-1080731029'),
('FFDE-DA-B-A-BCE-1807799782'),
('DDFAD-DDE-D-EB-FFDFBB-1683269209'),
('522822568-C-EFC--ACCA-EDB'),
('BDDAC-C-C-A-EC-450054394'),
('DBCD-D-AA-AD-EFA-385921800'),
('EF-B-ED-BBC-FEBC-599561669'),
('BED--F-BC-FD')

We have below sample records in the testTable:

--Get data from testTable
SELECT * FROM dbo.testTable

Now, to extract the first numeric value for each row of the table, we can use a SELECT query as below:

--Extract the first numeric value from the alphanumeric string
SELECT
val, SUBSTRING(val,
PATINDEX('%[0-9]%', val),
(CASE WHEN PATINDEX('%[^0-9]%', STUFF(val, 1, (PATINDEX('%[0-9]%', val) - 1), '')) = 0
THEN LEN(val) ELSE (PATINDEX('%[^0-9]%', STUFF(val, 1, (PATINDEX('%[0-9]%', val) - 1), ''))) - 1
END )
) AS Result
FROM dbo.testTable

Below is the output:

Understanding the query

To extract the first number from the given alphanumeric string, we are using a SUBSTRING function. In the substring function, we are extracting a substring from the given string starting at the first occurrence of a number and ending with the first occurrence of a character. Also, the position of the first occurrence of the character, which marks the end of the substring, must come after the position of the first occurrence of the number in the string.

In the above query, we are using PATINDEX function (which can deal with regular expressions) to identify the start of a number in the string:

PATINDEX(‘%[0-9]%’, val)

Then, we are using a CASE statement to extract the occurrence of a non-numeric character in the string. Before extracting the occurrence of the first non-numeric character, we are using a STUFF function to replace all the characters coming before the first occurrence of a number in the given string:

CASE WHEN PATINDEX(‘%[^0-9]%’, STUFF(val, 1, (PATINDEX(‘%[0-9]%’, val) – 1), ”)) = 0
THEN LEN(val) ELSE (PATINDEX(‘%[^0-9]%’, STUFF(val, 1, (PATINDEX(‘%[0-9]%’, val) – 1), ”))) – 1

Also, if there is no numeric value in the string, this query will output a NULL value as we have in the last row.

Thank you for the reading. Please share your input in the comment section of this post.