S_Kumar_S (10/5/2012)
This is quite impressive but it doesn't work for some scenarios. e.g. this one :insert into t1
select 'AAA22222222222222BB5BB 33CCCCCCCCC342234234'
EDIT: I meant it for Chris response:
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
My apologies, it was a change during coding:
SELECT
t.name, x.New_Name
FROM #t1 t
CROSS APPLY (
SELECT New_Name =
(SELECT Newletter + ''
FROM (
SELECT
n, Newletter = CASE WHEN ISNUMERIC(letter) = 1 -- d.grouper IS NOT NULL
AND COUNT(*) OVER (PARTITION BY d.grouper) > 5 THEN 'x' ELSE letter END
FROM (
SELECT t.name, n, letter,
grouper = n - ROW_NUMBER() OVER(ORDER BY ISNUMERIC(letter) desc, 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
Edit: found an error in an extended sample data set.
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