CODEWITHSUNDEEP
sql-servert-sqlnumberscharindex
user3519275
user3519275

Reputation: 370

Get only numeric part of a column data?

I have a column in my database containing both numeric and alphanumeric characters. I only want to get the numeric (6 numbers) from the column.

Example of data: 

TEST_123456_Prod
DB111111P
F222222FN
PROD999999_SCF

I want to create a select statement that returns all rows from this column where all but numbers are filtered out.

I´m using SQL Server, so probably Charindex needs to be used, but no idea how.

Upvotes: 2

Views: 2803

Answers (2)

Deepshikha
Deepshikha

Reputation: 10284

To strip off all alphanumeric characters you can create a function as:

CREATE FUNCTION [dbo].[RemoveAlphaCharacters](@InputString VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
  WHILE PATINDEX('%[^0-9]%',@InputString)>0
        SET @InputString = STUFF(@InputString,PATINDEX('%[^0-9]%',@InputString),1,'')     
  RETURN @InputString
END

GO

and then use it to get desired result as:

select dbo.RemoveAlphaCharacters(databasename)
from T1;

SQL Fiddle

Upvotes: 3

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20509

This will work for all of your examples:

SELECT 
    SUBSTRING(databasename, 
              PATINDEX('%[0-9]%', databasename), 
              LEN(databasename) - (PATINDEX('%[0-9]%', REVERSE(databasename)) + PATINDEX('%[0-9]%', databasename)) + 2)
FROM dbs

Here is a SQLFiddle with how the code works.

Upvotes: 2

Related Questions