Query to remove some characters from a column

Database
Highlighted
Enthusiast

Query to remove some characters from a column

Ex-

column contains values like- 

have your %[hidden_prod_class_desc]Q4_1% serviced by %[hidden_bo_name]Q3_1%

have your %[hidden_desc]Q14_1% serviced by %[hidden_bo_name]Q9_1%

what I need is-

have your [hidden_prod_class_desc] serviced by [hidden_bo_name]

have your [hidden_desc] serviced by [bo_name]

Tags (1)
3 REPLIES
Enthusiast

Re: Query to remove some characters from a column

Hi,

You can replace %[ with OReplace, but you will need to use a regular expression in conjunction with REGEXP_REPLACE to remove the ]Q1_1%.

Here is the code to handle the example above:

SELECT 

'have your %[hidden_prod_class_desc]Q4_1% serviced by %[hidden_bo_name]Q3_1%

have your %[hidden_desc]Q14_1% serviced by %[hidden_bo_name]Q9_1%' original_string,

REGEXP_REPLACE(

  OREPLACE(original_String,'%[','['), -- string to process,OREPLACE to replace the %[ with [

  '\]Q[0-9]+_[0-9]+%', -- regular expression to look for ]Q<some numbers>_<some numbers>%

  ']', -- string to replace it with

  1, --start position

  0, -- 0 = replace all occurences

  'i' -- case insensitive

  )

  

Regards,

Steve.

Senior Apprentice

Re: Query to remove some characters from a column

You might also get rid of the oReplace by using three capturing groups within the regex: replace all three groups by the 2nd:

REGEXP_REPLACE(original_String , '(%)(\[.+?\])(Q[0-9]+_[0-9]+%)', '\2', 1, 0, 'i')
Enthusiast

Re: Query to remove some characters from a column

Thank you very much steve.