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.
or
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
or
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
What a bizarre requirement!!! I can't come up with a real world scenario where any of these make sense. The regex suggestion won't really help here because you need to modify the values. All the regex is going to do is tell you if it matches some pattern, which you don't have.
I do however think that CLR would be the way to go here. You are going to have to loop through these strings character by character and t-sql just isn't the best way to do that.
I understand the requirements for the first and third versions but the second one I don't get why 'aaa' becomes 11 and why does 'd' become 4444????
For the first one you could do a pretty straight forward ascii calculation. Just force the string to upper first and then for each character take the ascii value and subtract 16.
The third one would be a modification to that logic, just need to track which character you are working with and a counter. When the character changes you output the counter and the same ascii calculation.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/