Hi Jacob nice post. I want to ask one question. Is there any way to get serial number or which string was first and which was second and so on after spliting the string? e.g 'Z,A,B' after spliting this I want 1 Z 2 A 3 B
DECLARE @x XML SELECT @x = CAST('' + REPLACE(@str, ',', '') + '' AS XML)
SELECT p.number as Position, x.value('.','VARCHAR(10)') AS Name FROM master..spt_values p cross APPLY @x.nodes('/i[position()=sql:column("number")]') n(x) where p.type = 'p' /* Position Name ----------- ---------- 1 Z 2 A 3 B */
This approach is explained in this post: http://blog.sqlserver.me/2008/08/tsql-lab-23-retrieving-values-and.html
DECLARE @x XML SELECT @x = CAST('' + REPLACE(@str, ',', '') + '' AS XML)
SELECT p.number as Position, x.value('.','VARCHAR(10)') AS Name FROM master..spt_values p cross APPLY @x.nodes('/i[position()=sql:column("number")]') n(x) where p.type = 'p'
Here is the new version. Replace [] with XML tags before you run the query.
DECLARE @str VARCHAR(50) SELECT @str = 'Z,A,B'
DECLARE @x XML SELECT @x = CAST('[i]' + REPLACE(@str, ',', '[/i][i]') + '[/i]' AS XML)
SELECT p.number as Position, x.value('.','VARCHAR(10)') AS Name FROM master..spt_values p cross APPLY @x.nodes('/i[position()=sql:column("number")]') n(x) where p.type = 'p' /* Position Name ----------- ---------- 1 Z 2 A 3 B */
Try this: DECLARE @t TABLE (id INT, data VARCHAR(100)) INSERT INTO @t SELECT 1,'Z,A,B' INSERT INTO @t SELECT 2,'K,C,D'
;WITH cte AS ( SELECT id, CAST('[i]' + REPLACE(data, ',', '[/i][i]') + '[/i]' AS XML) AS data FROM @t )
SELECT c.id, p.number as Position, x.value('.','VARCHAR(10)') AS Name FROM cte c CROSS JOIN master..spt_values p CROSS APPLY data.nodes('/i[position()=sql:column("number")]') n(x) where p.type = 'p' ORDER BY id, p.number /* id Position Name ----------- ----------- ---------- 1 1 Z 1 2 A 1 3 B 2 1 K 2 2 C 2 3 D */
Posts
Hi Jacob nice post.
ReplyDeleteI want to ask one question.
Is there any way to get serial number or which string was first and which was second and so on after spliting the string?
e.g
'Z,A,B'
after spliting this I want
1 Z
2 A
3 B
Is this possible?
How about using ROW_NUMBER()?
ReplyDeleteRow_number won't give me correct result. it will sort like this
ReplyDelete1 A
2 B
3 Z
You are right. Try the following:
ReplyDeleteDECLARE @str VARCHAR(50)
SELECT @str = 'Z,A,B'
DECLARE @x XML
SELECT @x = CAST('' + REPLACE(@str, ',', '') + '' AS XML)
SELECT
p.number as Position,
x.value('.','VARCHAR(10)') AS Name
FROM
master..spt_values p
cross APPLY @x.nodes('/i[position()=sql:column("number")]') n(x)
where p.type = 'p'
/*
Position Name
----------- ----------
1 Z
2 A
3 B
*/
This approach is explained in this post:
http://blog.sqlserver.me/2008/08/tsql-lab-23-retrieving-values-and.html
Hi Again, it returnd no rows :(
ReplyDeleteI'm on Sqlserver 2005 SP3
Show me the complete code that you are running. I doubt a mistake in the code. The sample code I posted above works in SQL Server 2005.
ReplyDeleteHold on, i see that blogger.com has removed the XML tags from my post. Let me post a new version.
ReplyDeleteDECLARE @str VARCHAR(50)
ReplyDeleteSELECT @str = 'Z,A,B'
DECLARE @x XML
SELECT @x = CAST('' + REPLACE(@str, ',', '') + '' AS XML)
SELECT
p.number as Position,
x.value('.','VARCHAR(10)') AS Name
FROM
master..spt_values p
cross APPLY @x.nodes('/i[position()=sql:column("number")]') n(x)
where p.type = 'p'
Here is the new version. Replace [] with XML tags before you run the query.
ReplyDeleteDECLARE @str VARCHAR(50)
SELECT @str = 'Z,A,B'
DECLARE @x XML
SELECT @x = CAST('[i]' + REPLACE(@str, ',', '[/i][i]') + '[/i]' AS XML)
SELECT
p.number as Position,
x.value('.','VARCHAR(10)') AS Name
FROM
master..spt_values p
cross APPLY @x.nodes('/i[position()=sql:column("number")]') n(x)
where p.type = 'p'
/*
Position Name
----------- ----------
1 Z
2 A
3 B
*/
any way we can get position() directly without joining with some number table?
ReplyDeleteI got it from your previous post.
Thanks!! you are great, this is much better than some split functions :)
one last question... sorry I'm bothering you too much.
ReplyDeleteHow to write same thing with a column and table?
No, you cannot use position() directly to retrieve the position of an element.
ReplyDeleteTry this:
ReplyDeleteDECLARE @t TABLE (id INT, data VARCHAR(100))
INSERT INTO @t SELECT 1,'Z,A,B'
INSERT INTO @t SELECT 2,'K,C,D'
;WITH cte AS (
SELECT
id,
CAST('[i]' + REPLACE(data, ',', '[/i][i]') + '[/i]' AS XML) AS data
FROM @t
)
SELECT
c.id,
p.number as Position,
x.value('.','VARCHAR(10)') AS Name
FROM cte c
CROSS JOIN master..spt_values p
CROSS APPLY data.nodes('/i[position()=sql:column("number")]') n(x)
where p.type = 'p'
ORDER BY id, p.number
/*
id Position Name
----------- ----------- ----------
1 1 Z
1 2 A
1 3 B
2 1 K
2 2 C
2 3 D
*/