Text Manipulation-Identify a 6 Character String and Replace with Ideal text

Database

Text Manipulation-Identify a 6 Character String and Replace with Ideal text

Hi

I have description column in a table that is pulling in a 6 character integer (like 123456) from somewhere. I would like to identify the 6 character string and replace it with the ideal text/number.

Complication: the 6 digit integer is not stable and it could any random combination (say 654321 or 124253 etc.,)

I have tried the following with little success;

select oreplace (description, '123456','£')

The above may be a solution but only for the specific case (the integer is 123456)..

Question: Is there a way of identifying any random 6 digit integer  combination in a string and replacing it with what I want?

Thank you in advance

Tee

1 REPLY
Senior Apprentice

Re: Text Manipulation-Identify a 6 Character String and Replace with Ideal text

Simply use a regular expression, this will replace exactly 6 digits:

REGEXP_REPLACE (description, '[0-9]{6}','£')