Oracle SQL query: Best way to remove unwanted characters?

Question

I want to remove all characters that are neither underscore, hyphen or alpha-numeric. Additionally, I don't want underscore or hyphen as the first character, so that needs to be removed as well.

This seems to mostly work using REGEXP_REPLACE and LTRIM:

SELECT LTRIM(
  REGEXP_REPLACE(
    '_-Hello(-)World)', 
    '[^-^_^a-z^A-Z^0-9]*', 
    ''),
  '_-') AS "result" 
FROM dual;

This correctly returns:

result
--------------------
Hello-World

However, for some reason this doesn't quite work when there is a line-break in the source string:

SELECT LTRIM(
  REGEXP_REPLACE(
    '_-Hello(-
)World)', 
    '[^-^_^a-z^A-Z^0-9]*', 
    ''),
  '_-') AS "result"
FROM dual; 

This instead returns "HelloWorld", i.e. without the hyphen:

result
--------------------
HelloWorld

There may be other issues with this solution as well that I have forgotten to mention. So, is there a better way to do what I'm trying to do?

Answer 1

'[^-^_^a-z^A-Z^0-9]*'

selects zero or more characters that are not (first circumflex) a hyphen, circumflex (second), underscore, circumflex (...), a to z, circumflex (...), A to Z, circumflex (to be sure) or zero to nine. So if you were to test with a text containing a circumflex (not on top of a vowel), it would surely remain, since you insist numerous times.

Also, if you'd happen to be using SQL*Plus, an interesting feature is the line continuation character, "-" or hyphen.

SQL> select 'multiple -
> lines' from dual;

'MULTIPLELINES'
---------------
multiple  lines

Perhaps read Continuing a Long SQL*Plus Command on Additional Lines. It explains about the disappearing hyphen.