OReplace vs. Case Statement

General

OReplace vs. Case Statement

Hello - I have a messy dataset that has values like this: 1=Very Dissatisfied, 2, 3...9, Very Satisfied=10. I have used a Case statement in the past to remove the words and '=' sign, but was thinking I could use the OReplace that was part of Teradata 14. Each time I try, it creates a new column for the response, but I have not been able to do multiple 'OReplace' for one results column. The end goal is to remove anything that is not a number, while keeping the number. Is Oreplace an option or should I stick with the Case statment?

Thanks - 

Allen

Tags (2)
3 REPLIES
Enthusiast

Re: OReplace vs. Case Statement

I am not clear with what you want. Can't assume much :).However, you can use an oreplace within another oreplace.. You can use oreplace in conjuction with case stmt  based on your requirement.

You can also think of using regexp

Maybe you can show what you have and what you want, so that it is easy to interpret.

Re: OReplace vs. Case Statement

Hi Raja - thanks for following up. I have data that is listed as 1=very dissatisfied, 2, 3...,9, very satisfied=10. I have been using the below case statement for each column in my data, but was hoping that OReplace would be able to look at all the data and remove the '=very dissatisfied' & 'very satisfied=10'.

,case when SRVY_RSPNS LIKE '%=10%'   THEN '10'

when srvy_rspns like '%1=%' then '1'

when srvy_rspns like '%know%' then null

else srvy_rspns

END as answer_txt

Let me know if this makes more sense, again, I appreicate the follow up and help on this matter.

Allen

Enthusiast

Re: OReplace vs. Case Statement

You can check this and see, implementing with your case when:

select regexp_replace('10=Very Satisfied','[^0-9]+','',1,0,'i')