Subscribe To

Subscribe to SQL Server and XML by Email
Labels

Thursday, August 14, 2008

XQuery Lab 19 - How to parse a delimited string?

This post is moved to http://beyondrelational.com/blogs/jacob/archive/2008/08/14/xquery-lab-19-how-to-parse-a-delimited-string.aspx

13 comments:

  1. 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

    Is this possible?

    ReplyDelete
  2. How about using ROW_NUMBER()?

    ReplyDelete
  3. Row_number won't give me correct result. it will sort like this
    1 A
    2 B
    3 Z

    ReplyDelete
  4. You are right. Try the following:

    DECLARE @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

    ReplyDelete
  5. Hi Again, it returnd no rows :(
    I'm on Sqlserver 2005 SP3

    ReplyDelete
  6. 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.

    ReplyDelete
  7. Hold on, i see that blogger.com has removed the XML tags from my post. Let me post a new version.

    ReplyDelete
  8. DECLARE @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'

    ReplyDelete
  9. 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
    */

    ReplyDelete
  10. any way we can get position() directly without joining with some number table?

    I got it from your previous post.
    Thanks!! you are great, this is much better than some split functions :)

    ReplyDelete
  11. one last question... sorry I'm bothering you too much.

    How to write same thing with a column and table?

    ReplyDelete
  12. No, you cannot use position() directly to retrieve the position of an element.

    ReplyDelete
  13. 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
    */

    ReplyDelete

About Me
Jacob Sebastian
Tutorials
* XQuery Tutorials
My Links
SQL Server White Papers
SQL Server 2008
My Articles
XML Workshop RSS Feed
Contact me
Readers
Free Hit Counter
Web Site Hit Counters
SQL Server Bloggers
Blog Directories
blogarama - the blog directory Programming Blogs - BlogCatalog Blog Directory
 
Copyright Jacob Sebastian