CODEWITHSUNDEEP
sqloracle-database
Vicky
Vicky

Reputation: 322

How to replace characters other than specified without using Regex in oracle sql?

How to replace characters other than specified without using Regular Expression in oracle sql?

For Example:

select replace ('rhythm','^h') from dual;

Expected result:

hh

Upvotes: 0

Views: 292

Answers (3)

Alex Poole
Alex Poole

Reputation: 191235

Seems like a non-problem since regular expressions exist, but you could do something like:

select listagg(c) within group (order by lvl) from
(
  select level as lvl,
    case when substr('rhythm', level, 1) in ('h', 'H')
      then substr('rhythm', level, 1) end as c
  from dual
  connect by level <= length('rhythm')
);

Any characters except h and H are reported as null by the subquery, and the listagg() ignores those nulls. You don't need to specify the characters to exclude, as you would with translate().

I know your example only had lower-case h but having an in() with a single entry would look odd, so I've included upper-case H to match what @Mottor did. It's extensible, anyway, so you can put any characters you're interested in into that list.

Upvotes: 2

Mottor
Mottor

Reputation: 1948

You can use TRANSLATE

SELECT TRANSLATE('rhythm', 'hHABCDEFGIJKLMNOPQRSTUVWXYZabcdefgijklmnopqrstuvwxyz', 'hH') 
  FROM DUAL;

And this here is for @Aleksej ;)

WITH t1 AS (SELECT 'rhythm bythm dubidah' s, 'ht' c FROM DUAL),
     t2 AS (SELECT TRANSLATE (s, '@' || c, '@') st FROM t1),
     t3 AS (SELECT LISTAGG (str, '') WITHIN GROUP (ORDER BY str) AS sm
           FROM (    SELECT UNIQUE SUBSTR (st, LEVEL, 1) str
                       FROM t2
                 CONNECT BY LEVEL <= LENGTH (st)))
SELECT TRANSLATE (s, c || sm, c) r
  FROM t1 CROSS JOIN t3;

Upvotes: 1

Aleksej
Aleksej

Reputation: 22949

You can use this, without need to write the characters to delete:

with test(string, character) as
(
 select 'rhythm','h' from dual
)
select rpad(character, length(string) - length(replace(string, character, '')),character)
from test

Upvotes: 1

Related Questions