Parsing a SQL Server string into substrings

Question

I have a column of data I've imported form a lotus notes database which consists of a contacted address field. The items in the address are separated by ASCII (13), ASCII (10)

What the easiest way to split this address up in to separate columns?

Answer 1

I use something like this in my projects. Unfortunatly I don't have my optimized version at hand but I coded it down quickly. This might get you started...

CREATE FUNCTION fx_Split
(
    @text varchar(max),
    @splitChar char(1)
)
RETURNS 
@Result TABLE 
(
    RowIndex int identity(1,1),
    SplitText varchar(max)
)
AS
BEGIN

    DECLARE @index int SET @index = 0
    DECLARE @SplitText varchar(max) SET @SplitText = ''
    DECLARE @TempText varchar(max) SET @SplitText = ''

    SET @index = CHARINDEX(@splitChar, @text) 
    SET @TempText = @text

    WHILE(@index > 0)
    BEGIN

        INSERT INTO @Result VALUES (SUBSTRING(@TempText, 1, @index-1))

        SET @TempText = SUBSTRING(@TempText, @index + 1, LEN(@TempText))

        SET @index = CHARINDEX(@splitChar, @TempText) 

    END 

    INSERT INTO @Result VALUES (@TempText)

    RETURN 
END
GO


select * from dbo.fx_Split ('asdf,qwer,asfegqgr,qweqwefe,qwf4ggrr,qfasdglsdfg', ',')