How to remove non-alphanumeric characters in SQL using Regex methods?-CodePudding

I want to strip all non-alphanumeric characters after the last alphanumeric character in one of my columns.

Sample Input:

my_column
weofneow.ewfiew')
wefkpfn.ewoifnw)
wepfnfe.ewfipn;

Sample Output:

my_column
weofneow.ewfiew
wefkpfn.ewoifnw
wepfnfe.ewfipn

My code right now looks like:

SELECT replace(replace(my_column, '\'', ''), ')', '') 
FROM my_table

However, I keep finding cases with other non-alphanumeric characters at the end of my string, and want a simple (not using functions) and clean (probably using RegEx) way to strip them out.

CodePudding user response：

You could use REGEXP_REPLACE with a regex of [^a-z0-9] $, replacing that with nothing. The regex will match any number of non-alphanumeric characters before the end of the string.

SELECT REGEXP_REPLACE(my_column, '[^a-z0-9] $', '', 1, 'i')
FROM my_table