Search This Blog

The best ParseString function T-SQL

So far this is the best one I could write, let me know if anyone has a better ParseString function

 

 

CREATE FUNCTION dbo.ParseString(@Str NVARCHAR(MAX), @Delim NVARCHAR(50))

RETURNS @T TABLE (Rn INT IDENTITY, Line NVARCHAR(MAX)) AS

BEGIN

SET @Str = REPLACE(@Str, NCHAR(10), '')

SET @Str = REPLACE(@Str, '&', '&')

SET @Str = REPLACE(@Str, '<', '&lt;')

SET @Str = REPLACE(@Str, '>', '&gt;')

SET @Str = REPLACE(@Str, '"', '&quot;')

SET @Str = REPLACE(@Str, '''', '&apos;')

DECLARE @xml XML

SET @xml = N'<r>' + REPLACE(@Str, @Delim, N'</r><r>') + N'</r>'

INSERT INTO @T (Line)

SELECT r.value('.','VARCHAR(MAX)') Lines

FROM @xml.nodes('/r') AS RECORDS(r)

RETURN

END

No comments: