I prefer to use
Common Table Expressions[
^].
Have a look here:
DECLARE @s VARCHAR(300)
SET @s = 'date=10/10/2000|age=13^date=01/01/2001|age=12^date=02/02/2005|age=8'
DECLARE @tmp TABLE(aDate DATETIME, aAge INT)
;WITH MyRows AS
(
SELECT LEFT(@s, CHARINDEX('^', @s) -1) AS MyRow, RIGHT(@s, LEN(@s) - CHARINDEX('^', @s)) AS Remainder
UNION ALL
SELECT LEFT(Remainder, CHARINDEX('^', Remainder) -1) AS MyRow, RIGHT(Remainder, LEN(Remainder) - CHARINDEX('^', Remainder)) AS Remainder
FROM MyRows
WHERE CHARINDEX('^', Remainder)>0
UNION ALL
SELECT Remainder AS MyRow, NULL AS Remainder
FROM MyRows
WHERE CHARINDEX('^', Remainder)=0
)
INSERT INTO @tmp (aDate, aAge)
SELECT CONVERT(DATETIME, SUBSTRING(MyRow, 6, 10)) AS aDate, CONVERT(INT,SUBSTRING(MyRow, 21, LEN(MyRow))) AS aAge
FROM MyRows
SELECT *
FROM @tmp
Result:
2000-10-10 00:00:00.000 13
2001-01-01 00:00:00.000 12
2005-02-02 00:00:00.000 8