Splitting delimited strings in SQL

April 25, 2014 2 Comments

This is an issue I come back to a couple of times a year and always forget how I did it last so here’s the info…

I recently found a great blog that compares all the different methods of splitting delimited strings in SQL Server (there are lots!), interestingly it isn’t always the same solution that is the fastest – It depends on the number of elements in your strings…

http://www.sqlservercentral.com/articles/Tally+Table/72993/

As I’m never expecting more than 30 elements in my strings I’ve settled on the XML method – I found a good example of it here http://www.sqlservercentral.com/articles/XML/66932/

However this wasn’t working for me as I have special characters in my strings such as * and / which are illegal characters in XML – To get around this I had a chat with our DBA and he suggested adding "<![CDATA[" to act as effectively a text qualifier. This works great, see the code below…

Apologies for the dodgy apostrophes in the code below – I’m working on it!

Since playing with this a bit I ran into a problem with strange ASCII characters in my strings which was causing it to fail – The trick was to find out what the character was (in my case it was a ‘record separator’) and the remove it using REPLACE and CHAR() – Mine was a CHAR(30).

 

CREATE FUNCTION [dbo].[ufnSplitXml]  
   (@List nvarchar(max), @Delim char(1)= ',')  
RETURNS @Values TABLE (Item nvarchar(100))AS  
  
--  This function accepts a parameter @List containing the string to be split eg 'abc,def,ghi'
--  The default delimiter is comma but you can specify others if required.
--  Use a cross apply to join to this table valued function.
  
  BEGIN 
  
    DECLARE @XML XML
    
    IF Right(@List,LEN(@Delim)) = @Delim
    BEGIN
          SET @List = LEFT(@List,LEN(@List) - Len(@Delim))
    END

    SET @xml = N'<t><![CDATA[' + REPLACE(@List,@Delim,']]></t><t><![CDATA[') + ']]></t>'  

    INSERT INTO @Values
    SELECT  r.value('.','varchar(MAX)') as Item  
    FROM  @xml.nodes('/t') as records(r)
 
  RETURN  
  END

Filed under SQL Server BI, Work Tagged with , , ,

Batching SQL Server Updates

March 13, 2014 Leave a comment

We sometime have to apply updates to very large tables of data, it’s usually wise to do this in batches.

Here’s an example of an update being applied in 100,000 row batches – The raiserror command puts a message in the Messages window to tell us which batch has just been completed.


DECLARE @i INT = 0,
@j INT,
@msg VARCHAR(100)

SELECT @j = MAX(HouseholdKey) FROM DimHousehold

WHILE @i <= @j
BEGIN
BEGIN TRAN
UPDATE dh
SET dh.AvgDriveTime = AvgDeliveryTime
FROM DimHousehold dh
INNER JOIN #OrderData fo
ON dh.HouseholdKey = fo.HouseholdKey
AND dh.DeliveryType = ‘Delivery’
WHERE dh.HouseholdKey BETWEEN @i AND @i+99999
COMMIT TRAN

CHECKPOINT

SELECT @msg = CAST(@i AS VARCHAR(20)) + ‘ – ‘ + CAST(@i+99999 AS VARCHAR(20)) + ‘ household keys updated’
RAISERROR(@msg,1,1) WITH NOWAIT
SET @i = @i + 100000
END




		
	


				


				

					

						Filed under SQL Server BI, Work
						Tagged with , , 					

				


			


		
			


				


					
Create a comma separated string


					

						

							March 11, 2011
																						Leave a comment
																				

					


					
I just used this again after 2 years so though I should flesh out the description a bit.


This is really useful if you need to quickly create a delimited string based on a GROUP BY statement.


The example below illustrates this using a simple set of sample data.


select 1 as id, ‘frog’ as name into #tmp

insert into #tmp (id, name) values (1,’toad’)

insert into #tmp (id, name) values (2,’cat’)

insert into #tmp (id, name) values (2,’dog’)


SELECT p1.Id, ( SELECT Name + ‘,’ FROM #tmp p2

WHERE p2.Id = p1.Id ORDER BY Name FOR XML PATH(”) ) AS list

FROM #tmp p1 GROUP BY Id ;




				


				

					

						Filed under SQL Server BI
						Tagged with , , , ,