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

Question

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:

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

Can you show an inclusive example of all the `IDS` values which we might be seeing in your table? Please show us more data. In the general case, doing that ugly replacement or maybe using a custom UDF are basically your options here. — Tim Biegeleisen, Sep 11 '18 at 10:57
@GordonLinoff so far yes, but ideally we want a solution that will cover it if they aren't in future — sqlproblem, Sep 11 '18 at 11:16
@sqlproblem . . . Then the UDF is the best solution, although you won't like the performance. — Gordon Linoff, Sep 11 '18 at 11:21
Possible duplicate of [T-SQL select query to remove non-numeric characters](https://stackoverflow.com/questions/18625548/t-sql-select-query-to-remove-non-numeric-characters) — Jayasurya Satheesh, Sep 11 '18 at 11:26

score 6 · Accepted Answer · answered Sep 11 '18 at 11:02

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

score 5 · Answer 2 · answered Sep 11 '18 at 11:45

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

score 0 · Answer 3 · answered Sep 11 '18 at 11:10

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

score -1 · Answer 4 · answered Sep 11 '18 at 11:05

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

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

4 Answers4

Linked