CODEWITHSUNDEEP
sqloracle-databaseregexp-replace
Ledinscak
Ledinscak

Reputation: 1

REGEXP_REPLACE back-reference with function call

Can I use some function call on REGEXP_REPLACE back-reference value?

For example I want to call chr() or any other function on back-reference value, but this

SELECT REGEXP_REPLACE('a 98 c 100', '(\d+)', ASCII('\1')) FROM dual;

just returns ASCII value of '\':

'a 92 c 92'

I want that the last parameter (replacement string) to be evaluated first and then to replace string. So result would be:

'a b c d'

Upvotes: 0

Views: 512

Answers (2)

Littlefoot
Littlefoot

Reputation: 142713

I'm not that smart to do it using one regular expression, but - step-by-step, something like this might help. It splits the source string into rows, checks whether part of it is a number and - if so - selects CHR of it. Finally, everything is aggregated back to a single string.

SQL> with test (col) as
  2    (select 'a 98 c 100' from dual),
  3  inter as
  4    (select level lvl,
  5            regexp_substr(col, '[^ ]+', 1, level) c_val
  6     from test
  7     connect by level <= regexp_count(col, ' ') + 1
  8    ),
  9  inter_2 as
 10    (select lvl,
 11            case when regexp_like(c_val, '^\d+$') then chr(c_val)
 12                 else c_val
 13            end c_val_2
 14     from inter
 15    )
 16  select listagg(c_val_2, ' ') within group (order by lvl) result
 17  from inter_2;

RESULT
--------------------
a b c d

SQL>

It can be shortened for one step (I intentionally left it as is so that you could execute one query at a time and check the result, to make things clearer):

SQL> with test (col) as
  2    (select 'a 98 c 100' from dual),
  3  inter as
  4    (select level lvl,
  5            case when regexp_like(regexp_substr(col, '[^ ]+', 1, level), '^\d+$')
  6                      then chr(regexp_substr(col, '[^ ]+', 1, level))
  7                 else regexp_substr(col, '[^ ]+', 1, level)
  8            end c_val
  9     from test
 10     connect by level <= regexp_count(col, ' ') + 1
 11    )
 12  select listagg(c_val, ' ') within group (order by lvl) result
 13  from inter;

RESULT
--------------------
a b c d

SQL>

[EDIT: what if input looks differently?]

That is somewhat simpler. Using REGEXP_SUBSTR, extract digits: ..., 1, 1 returns the first one, ... 1, 2 the second one. Pure REPLACE then replaces numbers with their CHR values.

SQL> with test (col) as
  2      (select 'a98c100e' from dual)
  3  select
  4    replace(replace(col, regexp_substr(col, '\d+', 1, 1), chr(regexp_substr(col, '\d+', 1, 1))),
  5                         regexp_substr(col, '\d+', 1, 2), chr(regexp_substr(col, '\d+', 1, 2))) result
  6  from test;

RESULT
--------------------
abcde

SQL>

Upvotes: 1

Alex Poole
Alex Poole

Reputation: 191275

Just for fun really, you could do the tokenization, conversion of numbers to characters, and aggregation using XPath:

select *
from xmltable(
  'string-join(
    for $t in tokenize($s, " ") 
      return if ($t castable as xs:integer) then codepoints-to-string(xs:integer($t)) else $t,
    " ")'
  passing 'a 98 c 100' as "s"
);

Result Sequence                                                                 
--------------------------------------------------------------------------------
a b c d

The initial string value is passed in as $s; tokenize() splits that up using a space as the delimiter; each $t that generates is evaluated to see if it's an integer, and if it is then it's converted to the equivalent character via codepoints-to-string, otherwise it's left alone; then all the tokens are recombined with string-join().

If the original has runs of multiple spaces those will collapse to a single space (as they will with Littlefoot's regex).

Upvotes: 1

Related Questions