CODEWITHSUNDEEP
sql
user2795318
user2795318

Reputation: 33

Substring replacement

I have string values like below

002 - KLAMPFER - Sy 52-5-5+L
002 - KLMGEEDD - SR 53-5-5+L
002 - LREFDGRE - SU 54-5 etc...

I want to make this like below output

002 - XXXXXXX - Sy 52-5-5+L
002 - XXXXXXX - SR 53-5-5+L
002 - XXXXXXX - SU 54-5

I tried but I did not get the output expected. I am looking for substring function with replace.

Upvotes: 0

Views: 93

Answers (2)

Viktor Török
Viktor Török

Reputation: 1319

Try to use regular expressions.

In Oracle the following SQL statement worked for me:

select regexp_replace('002 - KLAMPFER - Sy 52-5-5+L', '- [A-Z]+ -', '- XXXXXXX -')
  from dual;

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269773

This answers the original version of the question.

You can use update:

update t
    set col2 = 'XXXXXXX'
    where col2 = 'KLAMPFER';

If that value is a single string, then:

update t
    set col = replace(col, 'KLAMPFER', 'XXXXXXX')
    where col like '%KLAMPFER%';

Upvotes: 0

Related Questions