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.

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′,’;’

)

Share this post

Related Posts

Webinar Recap: CMMC News for the DIB

The DoD announcement on Nov 4 “Strategic Direction for the CMMC Program” provided new guidelines for CMMC 2.0. Our KTL webinar on Nov 10 helped to clarify these changes and answer questions for those in the DIB.

Read More »