Fetch Data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Fetch Data

Author

Topic

nirene
Yak Posting Veteran

98 Posts

Posted - 2013-02-28 : 00:56:35

Hi,

Data

Jaguar||61221031||4250||121725.13||9075129536
Land Rover||8984FFF498989||7878||890990||8977777
Range Rover||676767HQW7||8980||456567||999999999999999

Result expected

Jaguar,61221031
Land Rover,8984FFF498989
Range Rover,676767HQW7

I order to get the first column I used
SELECT LEFT(Fld, CHARINDEX('||', Fld) - 1) as First

How to get the second column?

Thanks in advance

Nirene

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-28 : 04:00:00

[code]
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

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources