Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345

The Joy of Numbers Expand / Collapse
Author
Message
Posted Monday, August 9, 2010 3:30 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:18 PM
Points: 1,276, Visits: 1,132
_ms65g_ (5/28/2010)
Remove duplicate side-by-side characters from a string
New approach using numbers table

CREATE FUNCTION dbo.fnRemoveDupesI (@String VARCHAR(8000)) 
RETURNS VARCHAR(8000) 
AS
BEGIN
    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 @result
END;



Be careful with the multirow concatenation thing you got going on there. You might be better off using the FOR XML PATH subquery method for ordered string concatenation (but then you have to worry about XML entities).

Thanks
Mike C
Post #966280
Posted Tuesday, August 17, 2010 10:24 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, December 24, 2012 11:32 AM
Points: 56, Visits: 880
Hi,
Here is a new approach for splitting a delimited string. It is set-based, without CHARINDEX, using number table…
See:

DECLARE @s VARCHAR(620)='item_01,item_02,item_03';
SET @s = ','+ @s + ',';

;WITH C AS
(SELECT n, ROW_NUMBER() OVER(ORDER BY n) AS rn
FROM Nums --Number Table
WHERE SUBSTRING(@s, n, 1) = ','
AND n <= LEN(@s))
SELECT SUBSTRING(@s, A.n + 1, B.n - 1 - A.n) AS item
FROM C A
INNER JOIN C B
ON A.rn + 1 = B.rn
AND B.n - A.n > 1;

Also here is the simplified version of my technique for removing duplicate side-by-side characters.

   DECLARE @result VARCHAR(8000) = '',
@string VARCHAR(8000) = 'SQQQQQQQQQQLLLLLLL Serrrrrrrrveerr';

SELECT @result = @result + Data
FROM (SELECT ID,
Data,
ROW_NUMBER() OVER (PARTITION BY Data ORDER BY ID) - ID
FROM (SELECT SUBSTRING(@String, n, 1), n
FROM Nums
WHERE n <= LEN(@String)
) D(data, ID)
) D(ID, Data, RowNum)
GROUP BY Data, RowNum
ORDER BY MIN(ID)

SELECT @result AS Result

Post #970585
Posted Saturday, August 21, 2010 11:11 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:11 AM
Points: 36,994, Visits: 31,513
_ms65g_ (8/17/2010)
Hi,
Here is a new approach for splitting a delimited string. It is set-based, without CHARINDEX, using number table…
See:

DECLARE @s VARCHAR(620)='item_01,item_02,item_03';
SET @s = ','+ @s + ',';

;WITH C AS
(SELECT n, ROW_NUMBER() OVER(ORDER BY n) AS rn
FROM Nums --Number Table
WHERE SUBSTRING(@s, n, 1) = ','
AND n <= LEN(@s))
SELECT SUBSTRING(@s, A.n + 1, B.n - 1 - A.n) AS item
FROM C A
INNER JOIN C B
ON A.rn + 1 = B.rn
AND B.n - A.n > 1;



That method is quite old, actually. Most people don't use it because, since it actually has to read the numbers table twice, it's usually slower than the CHARINDEX method.

If you'd like to convert the code (I don't want to be accused of writting biased code ) so that it can be played against a table with a CSV column in it, I'd be happy to demonstrate/backup my claim with code.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #972972
« Prev Topic | Next Topic »

Add to briefcase «««12345

Permissions Expand / Collapse