Post reply

how to create a table from the given string with row separator as "," and column separator as "|".

  • February 1, 2010 at 2:49 am

    #229527

    Hi Friends,

    how to create a table from the given string with row separator as "," and column separator as "|".

    For Eg. String = "ABC|123|!@#,DEF|456|$%^,XYZ|890|*&#"

    I will pass this string and i want the output as :

    Col1 Col2 Col3

    ABC 123 !@#

    DEF 456 $%^

    XYZ 890 *&#

    Thanks

    Aditya

  • February 1, 2010 at 3:06 am

    #1111484

    Hi

    Try this article http://qa.sqlservercentral.com/articles/T-SQL/63003/


    Clear Sky SQL
    My Blog[/url]

  • February 1, 2010 at 4:35 am

    #1111510

    Also consider not storing such strings in the database in the first place. If the original source of this data is a file, you can use any of the bulk import methods to split the data as it is loaded. This would normally be the absolute-fastest and best way to do this.

    There is an entire section of Books Online devoted to this starting here

    You might also consider using SSIS to do this, if the task is a regular one.

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • February 1, 2010 at 4:39 am

    #1111511

    actually i am passing this as a parameter from ASP.net application as a string and then in the Stored Procedure, i want to split the string and return a table, which i can use in the Stored procedure.

  • February 1, 2010 at 4:52 am

    #1111516

    asashank82 (2/1/2010)

    actually i am passing this as a parameter from ASP.net application as a string and then in the Stored Procedure, i want to split the string and return a table, which i can use in the Stored procedure.

    You seem to have invented XML 😉

    Have you considered passing XML instead and using the native XML support in SQL Server?

    As a separate point, you seem to be doing this:

    1. Stuff some useful data from a structure in the .NET application into a string

    2. Pass that long string to a SQL stored procedure

    3. Get SQL Server to parse that string back into something like the original .NET structure

    4. Store the resulting data somewhere in a table

    Is that about right? The more you can tell us, the better answer you will get 🙂

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • February 1, 2010 at 6:02 am

    #1111544

    yes. I want it the same way because this must be a common function returning a table.

    eg:

    @array varchar(max),

    @RowDelim varchar(10),

    @ColDelim varchar(10)

    I will pass the values to the above parameters from the frontend in this way:

    @array = 'a12,b23,c34|d,45e56,f67|g78,h89,i90'

    @RowDelim = '|'

    @ColDelim = ','

    and i need the output to be returning a table as :

    -----------------

    Col1 Col2 Col3

    -----------------

    a12 b23 c34

    d45 e56 f67

    g78 h89 i90

    -----------------

    i can t pass XML. I need to pass on String to the SP.

  • February 1, 2010 at 6:05 am

    #1111545

    Also as you are on 2008 , or at least this is a 2008 forum, use Table valued parameters.


    Clear Sky SQL
    My Blog[/url]

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply