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