CODEWITHSUNDEEP
sqlregexoracle-database
FrankSharp
FrankSharp

Reputation: 2632

Oracle regexp_replace with group

I try this

SELECT regexp_replace(v_gen,'(\b[a-zA-Z]+\b)','$$1%')INTO v_test FROM DUAL;

It supose to transform 'hello world word' in '$hello% $world% $word%'

The Oracle documentation is not clear about the founction regexp_replace with group...

Upvotes: 1

Views: 3302

Answers (1)

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

regexp_replace('hello world word','([a-zA-Z]+)','$\1%')

\1 gives you the string matching the pattern and then we format it appending the other characters required.

[a-zA-Z]+ looks for all continuous sequence of Alphabets.

([[:alnum:]]+) will look for sequence of alphanumeric characters. If you want one, you can use this.

Example: With Multiple options

with my_text as
(
  select 'hello world word' as str from dual
)
SELECT regexp_replace(str,'([[:alnum:]]+)','$\1%') FROM my_text
union all
SELECT regexp_replace(str,'([[:alpha:]]+)','$\1%') FROM my_text
union all
SELECT regexp_replace(str,'([a-zA-Z]+)','$\1%') FROM my_text

REGEXP_REPLACE(STR,'([
----------------------
$hello% $world% $word%
$hello% $world% $word%
$hello% $world% $word%

SQLFiddle Demo

Upvotes: 4

Related Questions