user6650224 user6650224 - 23 days ago 13
SQL Question

Remove special character from end of string

I am looking to remove special characters from the end of a string.

Input:

looking for Oracle help ~(
looking ~! for Oracle help ~( Stack
looking ~! for Oracle help ~( Stack ##


Output:

looking for Oracle help
looking ~! for Oracle help ~( Stack
looking ~! for Oracle help ~( Stack


So I just need to remove non-alphanumeric characters from the end of the string only.

I am looking for Oracle SQL query to achieve this.

Answer
select regexp_replace(str, '[^[:alnum:]]*$') from....

where str is the input string value. This will remove all the non-alphanumeric characters at the end of str ($ is anchoring at the end; * means as many as possible consecutive characters; [...] means character matching set, and ^ within the character matching set means negation). By not giving a third argument to regexp_replace(), the substring is replaced with nothing ('')

Comments