SQL - remove all letters (alpha characters) from a string-CodePudding

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?

CodePudding user response：

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.