Extract the first number from an alphanumeric string in sql server 4


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
Sample records in testTable

Sample records in 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:

Query to extract the first number from an alphanumeric string

Query to extract the first number from an alphanumeric string

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.

Rate This
[Total: 2    Average: 5/5]

Gopal Krishna Ranjan

About Gopal Krishna Ranjan

I am Gopal Krishna Ranjan, having 6+ years of industry experience in Software development using Microsoft technologies. I have a head down experience in Database development, performance tuning in SQL Server, T-SQL optimization, BI (Business Intelligence) project implementation, reporting in SSRS, using SSIS for ETL, implementing multi dimensional and tabular data-warehouse in SSAS, querying cubes using MDX and DAX, Windows and Web Applications development with C#.


Leave a comment

Your email address will not be published. Required fields are marked *

*

4 thoughts on “Extract the first number from an alphanumeric string in sql server

    • Gopal Krishna Ranjan
      Gopal Krishna Ranjan Post author

      Hi AndreevRu,
      Thank you for the comment.
      From the above alphanumeric string ‘__123__’, the output will be 123. For demonstration purpose, I have replaced the column name with a variable with an initial value of ‘__123__’ as below:

      declare @val varchar(100) = ‘___123___’
      –Extract the first numeric value from the alphanumeric string
      SELECT
      @val As InputValue, 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

      Output: 123

    • Gopal Krishna Ranjan
      Gopal Krishna Ranjan Post author

      Thanks for the comment.
      It will extract the 123 as first numeric value from the given string:

      declare @val varchar(100) = ‘___123___’
      –Extract the first numeric value from the alphanumeric string
      SELECT
      @val As InputValue, 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

      Output: 123