SQL Server 2008 Tip for Parsing Delimited Strings

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.


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


@parsedString TABLE (string NVARCHAR(80))


DECLARE @position int

SET @position = 1

SET @string = @string + @separator

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


INSERT into@parsedString

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

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




/*Example of how to execute the function*/

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


Share this post

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 »