SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 string splitting
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sigmas
Posting Yak Master

Belarus
172 Posts

Posted - 08/03/2013 :  08:12:52  Show Profile  Reply with Quote
Hi,
I have a string value like this:

"just_test <id = 1> some_value <id = 30> a_value <id = 5>"

I want the result set like this:

value |id
--------------
just_test |1
some_value |30
a_value |5


is it possible to implement that by using XML.

stepson
Constraint Violating Yak Guru

Romania
417 Posts

Posted - 08/03/2013 :  09:09:02  Show Profile  Reply with Quote
Hi,


declare @str as varchar(100)='just_test <id = 1> some_value <id = 30> a_value <id = 5>'
declare @strXML as xml

set @str =replace(@str,' <id = ','|') 
/* get rideoff last >*/
set @str = substring(@str,1,len(@str)-1) 
/*convert to cast .That could be done also in FROM */
set @strXML=cast('<root><element>'+ replace(@str,'>','</element><element>')+'</element></root>' as XML)


select 
 SUBSTRING(t.u.value('.','varchar(100)'),1,CHARINDEX('|',t.u.value('.','varchar(100)'))-1) as [value]
,SUBSTRING(t.u.value('.','varchar(100)'),CHARINDEX('|',t.u.value('.','varchar(100)'))+1,len(t.u.value('.','varchar(100)'))-CHARINDEX('|',t.u.value('.','varchar(100)'))) as [id]
from 
	(select @strXML as strXML)  m
	cross apply strXML.nodes('root/element') as t(u)



you can inspire from this:
http://visakhm.blogspot.in/2013/01/delimited-string-split-xml-parsing.html


it's ugly , maybe someone come with something more elegant ;)

S


Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb
Go to Top of Page

jethrow
Starting Member

USA
37 Posts

Posted - 08/04/2013 :  15:43:59  Show Profile  Reply with Quote
Here's a simple example w/o using XML:

declare @t table (value varchar(20), id varchar(5))
declare @str as varchar(100)='just_test <id = 1> some_value <id = 30> a_value <id = 5>'
declare @pos int = 1, @st int = 0, @field1 varchar(20)

set @pos = CharIndex('>', @str)
while @pos > 0 begin
	set @field1 = LTrim(SubString(@str, @st, @pos-@st))
	insert @t values (SubString(@field1,1,CharIndex(' ',@field1)-1),
			SubString(@field1,CharIndex('= ',@field1)+2,Len(@field1)))
	set @st = @pos+1
	set @pos = CharIndex('>', @str, @st)
end

Select * From @t;

Edited by - jethrow on 08/04/2013 15:45:38
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3583 Posts

Posted - 08/04/2013 :  20:31:17  Show Profile  Reply with Quote
Do you have to use XML sigmas? That is one of the most inefficient methods for splitting a string. The fastest that I have seen is Jeff Moden's string splitter here: http://www.sqlservercentral.com/articles/Tally+Table/72993/
The splitter function is in Figure 21
Go to Top of Page

productkeyfinding
Starting Member

1 Posts

Posted - 08/04/2013 :  23:09:47  Show Profile  Reply with Quote
unspammed
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000