How to remove the special characters in a string

Question

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?

score 2 · Accepted Answer · answered Feb 03 '22 at 06:38

2

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.

answered Feb 03 '22 at 06:38

Tim Biegeleisen

502,043
27
286
360

What does the '+' do? – StackOne Feb 03 '22 at 06:40
1

@StackOne The `+` is a quantifier meaning one or more of whatever precedes. In this case, then it means one or more digits. – Tim Biegeleisen Feb 03 '22 at 06:42

How to remove the special characters in a string

1 Answers1