RE: Nested replaces ? – SQLServerCentral

SSChampion

Points: 13369

January 17, 2013 at 5:13 am

Bhuvnesh (1/17/2013)
i havent got what you actually needed and asked ? can you post your requirement with sample data along with desired output. see "we cant see what you see"

An example what I am doing at the moment, but there are many variants on this:

I would like to have an interface like:

special_replace(@string, 'abcdefgh','12345678')

where all the 'a' get replaced by a 1,

all be 'b' get replaced by a 2, etc.

special_replace2(@string, 'aaa|bb|c|d|e|f|g|h','11|2|3|4444|5|6|7|8')

where the 'aaa' get's replaced by a 11

special_replace3(@string, '1111|111|11|1|2222|222|22|2','41|31|21|11|42|32|22|12')

where the '1111' get's replaced by a 41

where the '111' get's replaced by a 31

-- To find 'exotic characters' in a table

select '--' [--], COUNT(*) as tel from (

select distinct

REPLACE(

REPLACE(H,'a','')

,'b','')

,'c','')

,'d','')

,'e','')

,'f','')

,'g','')

,'h','')

,'i','')

,'j','')

,'k','')

,'l','')

,'m','')

,'n','')

,'0','')

,'p','')

,'q','')

,'r','')

,'s','')

,'t','')

,'u','')

,'v','')

,'w','')

,'x','')

,'y','')

,'z','')

,'0','')

,'1','')

,'2','')

,'3','')

,'4','')

,'5','')

,'6','')

,'7','')

,'8','')

,'9','')

,'8','')

AS H

FROM A_table

) as xxx

-- To find how significant the exotic characters are.

select distinct

REPLACE(

REPLACE(H,' ','')

,',','')

,'&','')

,'/','')

,'\','')

,'|','')

AS H

FROM A_table

) as xxx

-- to do some manipulation on the string.

select distinct

REPLACE(

REPLACE(H,' ','\1 ')

,',','\2')

,'&','\3')

,'/','\4')

,'\','\5')

,'|','\6')

AS H

FROM A_table

) as xxx

Thanks for your time and attention,

ben brugman