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