CODEWITHSUNDEEP
sqlsql-serverreplacealphanumeric
Sinnie
Sinnie

Reputation: 23

Remove all letters (alpha characters) from a string

I'm trying to write a function that removes any occurrence of any of the 26 alphabet letters from a string.

In: 'AA123A' -> Out: '123'

In: 'AB-123-CD% -> Out: '-123-%'

All I can find on Google is how to remove non-numeric characters, which all seem to be formed around defining the numbers you want to keep. But I want to keep any symbols too.

The 'simple' answer is 26 nested REPLACE for each letter, but I can't believe there isn't a better way to do it. I could define a string of A-Z and loop through each character, calling the REPLACE 26 times - makes the code simpler but is the same functionally.

Does anyone have an elegant solution?

Upvotes: 1

Views: 4587

Answers (2)

Martin Smith
Martin Smith

Reputation: 453277

Hopefully soon we will be able to use REGEXP_REPLACE rather than clunky methods like this but in the meantime you can use

DECLARE @StringToClean VARCHAR(1000) = 'AB-123-CD%'
DECLARE @CharsToRemove VARCHAR(100) = 'abcdefghijklmnopqrstuvwxyz'

SELECT  REPLACE(
            TRANSLATE(@StringToClean COLLATE Latin1_General_100_CI_AS, 
                      @CharsToRemove, 
                      REPLICATE(LEFT(@CharsToRemove, 1), LEN(@CharsToRemove + '.')-1)), 
            LEFT(@CharsToRemove, 1),
            '')

There is no need to list out all cases unless using a case sensitive collation (and the above ensures case insensitive semantics with the COLLATE clause)

The above uses the REPLICATE function to get the translations string of the right length (the reason for appending the dot and subtracting one from the length is to handle the case that the final character in @CharsToRemove was a space)

Upvotes: 0

vasja
vasja

Reputation: 4792

If I understand correctly, you can use TRANSLATE, e.g.:

SELECT REPLACE(TRANSLATE('AB-123-  CDdcba%', 'ABCDabcd','        '), ' ', '');
SELECT REPLACE(TRANSLATE('AB-123-  CDdcba%', 'ABCDabcd','AAAAAAAA'), 'A', '');
  • first case trimming also spaces,
  • second one, preserving existing spaces.

Just add the rest of characters to 'ABCDabcd' argument and keep 'AAAAAAAA' same length as the second argument.

Upvotes: 1

Related Questions