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:
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.