July 3, 2013 at 8:04 am
Hello,
First of all, i apologize if this was asked before. I did search before asking, so please don't get mad:-)
I have a string (nvarchar). Using replace, i can replace a character in this string with another one.
But what if i wanted to do something like this:
select replace(replace('abc', 'a', 'b'), 'b', 'z')
Not only that, but what if i want to replace 10 characters. The code will look monstrous:
SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE('abcdefghijklmn',
'a', 'b'), 'b',
'c'), 'c', 'd'),
'd', 'e'), 'e',
'f'), 'f', 'g'), 'g',
'h'), 'h', 'j'), 'j', 'k'), 'k', 'i')
Is there a better way, that will not sacrifice the performance? I tried using a recursive CTE, but the performance is really really bad.
Thank you for your time:)
Cheers,
Radu
July 3, 2013 at 8:15 am
well, nested REPLACE functions are extremely efficient, even if they are wordy and make your eyes cross when nested more than a couple of times.
format them for readability, but the way you have it is going to work.
you could potentially use a inline table value function , or a slower scalar function, but that really depends on the real requirement;
what values are you really replacing?
is is a 1:1 char for char, an kind of /obfuscation/encryption algorythm, or replacing high ascii with proffered chars, or what are you really doing ?
Lowell
July 3, 2013 at 8:27 am
The initial goal was to replace all diacritics (specifically, replace all diacritics used in romanian with their corresponding character) but then that got me thinking there has to be a better way to replace char for char in a string.
I will try to see if a SQL CLR function using NormalizationForm is fast. I hate seeing T-SQL code like those horrible nested replace(replace(....)).
Thx,
Radu
July 3, 2013 at 9:15 am
ok, i've done something kind of similar,and easily adaptable..
i'm guessing that, for example, you really want to replace any letters À Á Â Ã Ä Å with A, is that right? and so on for the whole high ascii character set?
i've got a table value function that does that, but you are limited to 100 replaces, due to the recursion rules for a function.
in my case, i was cleaning up html to make them more html compliant, and vice versa...converting html into reports.
here's a code example, but because the forum handles html symbols, i have to post a link to the procedure instead of pasting it here:
Lowell
July 3, 2013 at 9:25 am
I don't think there's a better way to do this in general. T-SQL isn't great at string manipulation.
July 3, 2013 at 9:47 am
i also found this old scalar function that uses a Tally Table to find and replace, based on the ASCII code...
this actually does the replacements of À Á Â Ã Ä Å with A, but for the entire charset from 126-255.
that function does not confirm to any specific set of conversion rules, just something that i slapped together for a postthat made sense to the rules at that time:
Lowell
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply