CODEWITHSUNDEEP
sqlregexp-substr
StackOne
StackOne

Reputation: 286

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?

Upvotes: 0

Views: 149

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521437

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.

Upvotes: 2

Related Questions