Search code examples
sqlregexp-substr

How to remove the special characters in a string

Here is the situation

WITH q AS (SELECT '( This is Z12783)' AS sentence FROM DUAL)
SELECT REGEXP_SUBSTR(sentence,'Z[0-9].*')
FROM q;

Desired Output: Z12783

But the Output I get is: Z12783)

Is there a way to remove the ')' at the end within this REGEXP_SUBSTR function?

Solution

  • Your regex pattern is slightly off. Use this version:

    WITH q AS (SELECT '( This is Z12783)' AS sentence FROM DUAL)
SELECT REGEXP_SUBSTR(sentence,'Z[0-9]+')
FROM q;

    Your current regex pattern Z[0-9].* actually says to match Z, followed by one digit, followed by the rest of the string. You mean to use Z[0-9]+, which matches Z followed one or more digits only.