KTL SOLUTIONS

SQL Server 2008 Tip for Parsing Delimited Strings

Share this post

I recently had a project where I needed to migrate data from a source system to Dynamics-GP. For the most part it was fairly straight forward with the exception that this source system stored it’s period data in a delimited string in a field. I needed an easy way to get the data out so that I can store the period data in separate records and fields. I came across a script that got me part of the way there and I turned it into a SQL Server 2008 function that I thought may help others. Here it is, basically the function takes two parameters, the string and what the delimiter is. It then parses the string and returns it as a table for you to work with.

CREATE

FUNCTION[dbo].fnParseString (@string NVARCHAR(4000),@separator NCHAR(1))

RETURNS

@parsedString TABLE (string NVARCHAR(80))

AS

BEGIN
DECLARE @position int

SET @position = 1

SET @string = @string + @separator

WHILE charindex(@separator,@string,@position) <> 0

BEGIN

INSERT into@parsedString

SELECT substring(@string, @position, charindex(@separator,@string,@position) – @position)

SET @position = charindex(@separator,@string,@position) + 1

END

RETURN

END

/*Example of how to execute the function*/

select * fromdbo.fnParseString(’17;0;0;0;0;0;0;0;0;0;0;0;0′,’;’

)

Related Posts

Checking Your CMMC Progress

Written by Alec Toloczko With Cybersecurity Maturity Model Certification (CMMC) requirements on the horizon, it’s crucial for organizations handling Controlled Unclassified Information (CUI) to adhere

Read More »