;WITH L0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rowsL1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),--4 rowsL2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),--16 rowsL3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),--256 rowsL4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),--65536 rowsL5 AS (SELECT 1 AS C FROM L4 AS A, L4 AS B),--4294967296 rowsNums AS (SELECT ROW_NUMBER() OVER (ORDER BY C) AS n FROM L5)SELECT N AS i INTO NUMBERS_TEST FROM Nums WHERE n <= 100000000

;WITH L0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rowsL1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),--4 rowsL2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),--16 rowsL3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),--256 rowsL4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),--65536 rowsL5 AS (SELECT 1 AS C FROM L4 AS A, L4 AS B),--4294967296 rowsNums AS (SELECT ROW_NUMBER() OVER (ORDER BY C) AS n FROM L5)SELECT ISNULL(N,0) AS i INTO NUMBERS_TEST FROM Nums WHERE n <= 100000000

DECLARE @s VARCHAR(500)='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaffffffffffffffffffffffffdddddddddddd ghqwer a d qqqq [pppp]'DECLARE @result VARCHAR(500)='';WITH c AS (SELECT 1 AS n UNION ALL SELECT 1 + n FROM c WHERE n < 100),c1 AS (SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT 1)) FROM c c1 CROSS JOIN c c2), k AS(SELECT n, k = SUBSTRING (@s, n, 1) FROM c1 WHERE n <= LEN(@s))SELECT @result = @result + kFROM k k1WHERE NOT EXISTS (SELECT * FROM k k2 WHERE k1.k = k2.k AND k1.n+1 = k2.n);SELECT @result AS removed;/*removed-----------------------afd ghqwer a d q [p]

DECLARE @s VARCHAR(80)='Army,Navy,Air Force,Marines';WITH c AS (SELECT 1 AS n UNION ALL SELECT n+1 FROM c WHERE n < 100)SELECT i FROM (SELECT CASE WHEN CHARINDEX(',', @s + ',', n) - n = 0 THEN '' ELSE SUBSTRING(@s, n, CHARINDEX(',', @s + ',', n) - n) END, n FROM c WHERE n <= LEN(@s) ) d(i, n)WHERE SUBSTRING(',' + @s, n, 1) = ','

;WITH C (i) AS(SELECT '0' UNION SELECT '1' UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5'UNION SELECT '6' UNION SELECT '7' UNION SELECT '8' UNION SELECT '9')SELECT c1.i + c2.i + c3.i + c4.i + 1 AS nbr --10000 numbersFROM C c1, C c2, C c3, C c4

--===== This method causes all 10000 rows to show up in the execution plan even thouth only 100 -- are called for. It also has the disadvantage of not being sorted. I wouldn't use this method.;WITH C (i) AS( SELECT '0' UNION ALL SELECT '1' UNION ALL SELECT '2' UNION ALL SELECT '3' UNION ALL SELECT '4' UNION ALL SELECT '5' UNION ALL SELECT '6' UNION ALL SELECT '7' UNION ALL SELECT '8' UNION ALL SELECT '9'),cteTally AS(SELECT c1.i + c2.i + c3.i + c4.i + 1 AS nbr --10000 numbersFROM C c1, C c2, C c3, C c4) SELECT nbr FROM cteTally t WHERE nbr BETWEEN 1 AND 100 ;--===== Although a bit more complicated, this method is very effective because it only generates the -- number of rows requested AND has the advantage of a sorted result set.WITH E1(N) AS ( --=== Create Ten 1's SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 --10 ), E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100 E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E4) SELECT N FROM cteTally WHERE N BETWEEN 1 AND 100

DECLARE @s VARCHAR(80);SELECT @s = 'Army,Navy,Air Force,Marines';--===== Your splitter with Itzik's inline Tally tableWITH E1(N) AS ( --=== Create Ten 1's SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 --10 ), E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100 E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) FROM E4) --===== Do your split SELECT i FROM (SELECT CASE WHEN CHARINDEX(',', @s + ',', n) - n = 0 THEN '' ELSE SUBSTRING(@s, n, CHARINDEX(',', @s + ',', n) - n) END, n FROM cteTally WHERE n <= LEN(@s) ) d(i, n)WHERE SUBSTRING(',' + @s, n, 1) = ',';--===== An even simpler splitter, also with Itzik's inline Tally tableWITH E1(N) AS ( --=== Create Ten 1's SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 --10 ), E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100 E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) FROM E4) --===== Do the split SELECT SUBSTRING(@s, N, CHARINDEX(',', @s + ',', N) - N) AS Item FROM cteTally WHERE N < LEN(@s) + 2 AND SUBSTRING(',' + @s, N, 1) = ',';

CREATE FUNCTION dbo.fnRemoveDupesI (@String VARCHAR(8000)) RETURNS VARCHAR(8000) ASBEGIN DECLARE @result VARCHAR(8000) = ''; ;WITH DataOrder AS ( SELECT ID, Data ,ROW_NUMBER() OVER (PARTITION BY Data ORDER BY ID) AS RowNum FROM (SELECT SUBSTRING(@String, nbr, 1), nbr FROM Nums WHERE nbr <= LEN(@String) ) D(data, ID) ) SELECT @result = @result + Data FROM (SELECT ID, Data ,DENSE_RANK() OVER (ORDER BY ID - RowNum) As [Rank] FROM DataOrder )D GROUP BY Data, [Rank] ORDER BY MIN(ID) RETURN @resultEND;