none
Parsing data RRS feed

  • Question

  • Hi all

    i have a rek.i have a column >>102392-293,98390283-109,1928374-495

    now i want the o/p as

    102392-293
    98390283-109
    1928374-495

    Its parsing the column based on the comma.The Comma seperated values here are jus 3,but it may range upto 10 values.
    I have done this in sql server user defined function.In my proj i have to parse 80 million rows of these kind.SO many suggested me to go for c# or vb.net parsing.So can anyone tell me the code for this funciton in c# and how can i use that function in sql server 2005.
    I want to call this function from a stored proc and do a cross apply.

    Thanks
    MohanV
    • Edited by SSDL Friday, February 19, 2010 8:57 PM correction
    Friday, February 19, 2010 4:36 PM

Answers

All replies

  • declare @xml xml, @s nvarchar(max)
    
    select @s = '102392-293,98390283-109,1928374-495'
    
    
    set @xml = N'<root><r>' + replace(@s,',','</r><r>') + '</r></root>'
    
    select 
      t.value('.','varchar(max)') as [delimited items]
    from @xml.nodes('//root/r') as a(t)

    Busy in learning!!
    Saturday, February 20, 2010 2:58 PM
  • The String class' Split function would also work, there's SQLCLR versions of this floating around on the internet. If it gets more complicated you could go to regular expressions.

    Cheers,
    Bob
    Saturday, February 20, 2010 9:48 PM
    Moderator
  • You can find CLR examples (and T-SQL as well) at http://www.sommarskog.se/arrays-in-sql-2005.html.
    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Proposed as answer by Naomi N Sunday, February 28, 2010 6:11 AM
    • Marked as answer by KJian_ Monday, March 1, 2010 2:18 AM
    Sunday, February 21, 2010 5:01 PM
  • Can you give me the code for that in c#.Am new to c#
    Monday, February 22, 2010 6:42 PM
  • Hi Mohanv,

    I think Dan’s reply should work. Please refer to the CLR Functions Using Split part.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, February 23, 2010 5:43 AM