S_Kumar_S (10/5/2012)
Hi Chris.the numbers are not consecutive as you have assumed. And they may be of length greater than 6. I think it is not so straight as it looks.
Ah, ok.
SELECT
t.name, x.New_Name
FROM #t1 t
CROSS APPLY (
SELECT New_Name =
(SELECT Newletter + ''
FROM (
SELECT n, Newletter = CASE WHEN d.grouper IS NOT NULL
AND COUNT(*) OVER (PARTITION BY d.grouper) > 5 THEN 'x' ELSE letter END
FROM (
SELECT n, letter,
grouper = ROW_NUMBER() OVER(PARTITION BY t.name ORDER BY ISNUMERIC(letter), n) - N
FROM (SELECT TOP(LEN(t.name)) n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM sys.columns a, sys.columns b) tally
CROSS APPLY (SELECT letter = SUBSTRING(t.name,n,1)) l
) d
) stri
ORDER BY n
FOR XML PATH('') , TYPE).value('.', 'varchar(max)')
) x
ORDER BY t.name
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden