declare @test table
(
Data varchar(100)
)
insert @test
values ('Jaguar||61221031||4250||121725.13||9075129536')
,('Land Rover||8984FFF498989||7878||890990||8977777')
,('Range Rover||676767HQW7||8980||456567||999999999999999')
SELECT m.n.value('./Node[1]','varchar(100)') AS [Column1],
m.n.value('./Node[2]','varchar(100)') AS [Column2]
FROM (SELECT CAST('<Root><Node>' + REPLACE(Data,'||','</Node><Node>') + '</Node></Root>' AS xml) AS XMLData FROM @test)t
CROSS APPLY XmlData.nodes('/Root') m(n)
output
------------------------------
Column1 Column2
------------------------------
Jaguar 61221031
Land Rover 8984FFF498989
Range Rover 676767HQW7
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/