The "replace Nth character" doesn't actually catch the Nth instance of a particular character, it just replaces the Nth character.

Try it on "Susy sells sea shells by the sea shore.", and try using it to replace the third "s". As written, it doesn't even have an input parameter (variable) for which character to replace, nor does adding one to the case statement accomplish this.

What it really does is simply replace the third character (or whichever character you use in the case statement). Since that can be done much more easily with Stuff, I don't see a purpose for that particular piece of code. If you really want to replace the third "s" in that string, here's how I'd go about it:

declare @String varchar(1000), @String2 varchar(1000),

@Letter char(1), @Occurance int, @Replace char(1)

select @String = 'Susy sells sea shells by the sea shore.',

@String2 = '',

@Letter = 's',

@Occurance = 3,

@Replace = 'X'

;with Chars ([Char], [Pos], Ord) as

(select substring(@String, number, 1),

row_number() over (partition by substring(@String, number, 1) order by number),

number

from common.dbo.smallnumbers

where number between 1 and len(@String))

select @String2 =

@String2 +

case

when [Char] = @Letter and [Pos] = @Occurance then @Replace

else [Char]

end

from Chars

order by ord

select @String2

As far as parsing out a string character-by-character, a Numbers table can do that just as easily and just as fast, and can deal with arbitrarily large strings. That's mentioned in the article, but I have to ask: Why have two different functions for the same thing? If you're going to have one based on a Numbers table, why also have one based on a CTE?

For the phone number and other regex type functions, speed tests have been done by members of this site, and we found that a CLR regex out-performs this CTE method. Testing and discussion at:

http://www.sqlservercentral.com/Forums/Topic470379-338-1.aspx

The article is okay, but there are better solutions for each of these things.

(In the Replace Nth CTE I built above, I reference my Common database and SmallNumbers table. I keep a database, called "Common" with a number of tables and functions that other databases reference. Much like a code library. The SmallNumbers table is 0 - 1,000.)