CODEWITHSUNDEEP
sqlsql-server
sqlproblem
sqlproblem

Reputation: 33

How to find and remove alphabet letters in in a column on MS sql server

I have a column of alphanumeric IDs let's call it [IDS].

The id's are meant to be numbers only, but some of them have stray characters.

For example:

[IDS]

 - 012345A
 - 23456789AF
 - 789789

I want to turn these into numbers only - so the output would be:

[IDS]
012345
23456789
789789

I want to write some code that will search the column for all and any letters in the alphabet (A-Z) and remove them so I can extract the numeric value.

I know I could do a replace(replace(replace(....etc but for all 26 letters in the alphabet this isn't ideal.

I am now trying to solve it using a "declare @" but these seem to be designed for specific strings and I want the whole column to be searched and replaced.

Using Microsoft SQL Server.

Thanks

Upvotes: 3

Views: 10581

Answers (4)

SQL_M
SQL_M

Reputation: 2475

Gotta throw this ugly beast in here...

SELECT REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE(REPLACE (
       REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE(REPLACE (
       REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE(
       REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE 
      (IDS, 'A', ''), 'B', ''), 'C', ''), 'D', ''), 'E', ''), 'F', ''), 'G', '') 
                    , 'H', ''), 'I', ''), 'J', ''), 'K', ''), 'L', ''), 'M', '') 
                    , 'N', ''), 'O', ''), 'P', ''), 'Q', ''), 'R', ''), 'S', '')
                    , 'T', ''), 'U', ''), 'V', ''), 'W', ''), 'X', ''), 'y', '')
                    , 'Z', '')    
FROM #Table11

Upvotes: 5

Barbaros Özhan
Barbaros Özhan

Reputation: 65408

You may create a function :

CREATE FUNCTION getNumber(@string VARCHAR(1500))
    RETURNS VARCHAR(1500)
AS
BEGIN
    DECLARE @count int
    DECLARE @intNumbers VARCHAR(1500)
    SET @count = 0
    SET @intNumbers = ''

    WHILE @count <= LEN(@string)
    BEGIN 
        IF SUBSTRING(@string, @count, 1)>='0' and SUBSTRING (@string, @count, 1) <='9'
            BEGIN
                SET @intNumbers = @intNumbers + SUBSTRING (@string, @count, 1)
            END
        SET @count = @count + 1
    END
    RETURN @intNumbers
END
GO

and then call it :

SELECT dbo.getNumber('23456789AF') As "Number"

Number
23456789

Rextester Demo

Upvotes: 0

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11609

First create this UDF

CREATE FUNCTION dbo.udf_GetNumeric
(@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @intAlpha INT
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
BEGIN
WHILE @intAlpha > 0
BEGIN
SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
END
END
RETURN ISNULL(@strAlphaNumeric,0)
END
GO

Now use the function as

SELECT dbo.udf_GetNumeric(column_name) 
from table_name

SQL FIDDLE

I hope this solved your problem.

Reference

Upvotes: -1

Chanukya
Chanukya

Reputation: 5893

CREATE TABLE #Table11
    ([IDS] varchar(10))
;

INSERT INTO #Table11
    ([IDS])
VALUES
    ('012345A'),
    ('23456789AF'),
    ('789789')
;


SELECT SUBSTRING([IDS], PATINDEX('%[0-9]%', [IDS]), PATINDEX('%[0-9][^0-9]%', [IDS] + 't') - PATINDEX('%[0-9]%', 
                    [IDS]) + 1) AS IDS
FROM #Table11

output

IDS
012345
23456789
789789

Upvotes: 6

Related Questions