Return to Question

formatting queries

Source Link

edited Dec 14, 2021 at 15:40

6.5k
4
20
39

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?

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?

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?

Source Link

asked Dec 14, 2021 at 13:56

dbdemon

6.5k
4
20
39

Oracle SQL query: Best way to remove unwanted characters?

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?

oracle oracle-11g-r2