September 10, 2012 at 7:38 am
New week, new ideas and a new solution (it might not be the best, but it's new).
I haven't compared it with Dwain's solution.
It's prepared to change one or two characters with multiple appearances on each string.
DECLARE @test-2TABLE(
stringvarchar(100))
INSERT @test-2
SELECT 'L@*K@*H@/324' AS string
UNION ALL SELECT '(AF#-AR#)*(Y#+BB#)'
UNION ALL SELECT 'TT#*(L#+D@)*L@'
UNION ALL SELECT '((AE#-AR#)*(Y#+A#))/Y#';
DECLARE@Searchedvarchar(2),
@Newvarchar(2),
@Lengthint
SET @Searched = 'L'
SET @New = 'XY'
SET @Length = LEN( @Searched)
WITH Data AS(
SELECTCAST( '+' + string AS varchar( 101)) string,
1 AS n
FROM @test-2
UNION ALL
SELECT CAST( CASE WHEN SUBSTRING( d.string, n, @Length) = @Searched
AND SUBSTRING( d.string, n - 1, 1) LIKE '[^A-Z]'
AND SUBSTRING( d.string, n + @Length, 1) IN ( '#', '@', '&')
THEN STUFF( d.string, n, @Length, @New)
ELSE d.string END AS varchar( 101)),
n + 1 AS n
FROM Data d
WHERE n < LEN( d.string))
SELECT STUFF( d.string,1,1,'') AS string
FROM Data d
WHERE LEN( d.string) = d.n
September 10, 2012 at 7:56 am
dwain.c, Mark, and Luis, thank you very much for your solutions. All three are workable and really quite brilliant. Ultimately I believe I'll be going with Luis's suggestion, as it's the only one I don't seem to be able to break based on the length of the "variable" values I feed it. I know I can tweak the other two to behave the same, but Luis's appears solid right out of the box.
I greatly appreciate the effort all three of you put into this. I only hope I can return the favor some day.
Thanks!
ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
September 10, 2012 at 8:04 am
Well, I could really use an annual pass to Disney World 😀
I'm really glad I could help you.
September 10, 2012 at 5:58 pm
ronmoses (9/10/2012)
dwain.c, Mark, and Luis, thank you very much for your solutions. All three are workable and really quite brilliant. Ultimately I believe I'll be going with Luis's suggestion, as it's the only one I don't seem to be able to break based on the length of the "variable" values I feed it. I know I can tweak the other two to behave the same, but Luis's appears solid right out of the box.I greatly appreciate the effort all three of you put into this. I only hope I can return the favor some day.
Thanks!
ron
Mark's and Luis's solutions were definitely much less ugly, so I can't blame you.
At least mine was in the running for awhile.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 10, 2012 at 6:05 pm
dwain.c (9/10/2012)
At least mine was in the running for awhile.
Truth be told, I was literally in the process of typing up my response declaring you the winner, when Luis's code came in. I had to try it out, just to be fair, and the rest is history. 🙂
Thanks!
Ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply