CODEWITHSUNDEEP
sql-server
MrUnknown
MrUnknown

Reputation: 25

Remove all Non-Numeric Chars from String

I would like to truncate all characters in a column, no matter where they are.

Example: "+49123/4567890(testnumber)"

Should be changed to "491234567890"

Is there a way without doing a replace for each char?

I have tried to replace it with several, but it is very time-consuming.

Upvotes: 2

Views: 4945

Answers (3)

Martin Smith
Martin Smith

Reputation: 452978

Instead of hardcoding the list of "bad" characters you can use a double TRANSLATE to first get the unwanted characters and then plug that back into TRANSLATE.

DECLARE @table TABLE
  (
     str VARCHAR(max)
  )

INSERT INTO @table
SELECT '+49123/4567890(testnumber)    '

DECLARE @CharactersToKeep VARCHAR(30) = '0123456789'

SELECT REPLACE(TRANSLATE(str, bad_chars, REPLICATE('X', LEN(bad_chars + 'X') - 1)), 'X', '')
FROM   @table
       CROSS APPLY (SELECT REPLACE(TRANSLATE(str, @CharactersToKeep, REPLICATE(LEFT(@CharactersToKeep, 1), LEN(@CharactersToKeep))), LEFT(@CharactersToKeep, 1), '')) ca(bad_chars)

Upvotes: 0

Stu
Stu

Reputation: 32579

A set-based option that exists in SQL Server 2017+ is to utilise translate.

You can hopefully adapt the following to your specific use-case:

select col, Replace(Translate(col, r, Replicate('*', Len(r))), '*', '') Newcol
from t
cross apply(values(' ABCDEFGHIJKLMNOPQRSTUVWXYZ/\+()'))r(r);

Example DB<>Fiddle

Upvotes: 1

PSK
PSK

Reputation: 17943

As you mentioned, if you are expecting only [a-zA-z()/+], you can use the translate function which is available from 2017+

declare @table TABLE (str varchar(max))
insert into @table
select '+49123/4567890(estnumber)'

select replace(translate(str, '/+()abcdefghijklmnopqrstuvwxyz', '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'), '~', '') digits
from @table

For more complex scenarios where the characters are not known, you can try using recursive CTE on a string column to extract only digits like following query.

;with cte
as (
    select v.txt originalstring
        ,v.txt
        ,convert(varchar(max), '') as digits
        ,1 as lev
    from (
        values ('+49123/4567890(testnumber)')
            ,('+234&*#$%!@#')
        ) v(txt)
    
    union all
    
    select originalstring
        ,stuff(txt, 1, 1, '')
        ,(
            case 
                when left(txt, 1) LIKE '[0-9]'
                    then digits + left(txt, 1)
                else digits
                end
            )
        ,lev + 1
    from cte
    where txt > ''
    )
select originalstring
    ,digits
from (
    select c.originalstring
        ,c.digits
        ,row_number() over (partition by c.originalstring order by lev desc
            ) rn
    from cte c
    ) t
where rn = 1

Output

originalstring                    digits
---------------                   --------
+234&*#$%!@#                      234
+49123/4567890(testnumber)        491234567890

Upvotes: 2

Related Questions