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

Question

Post reply

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

asashank82

SSC Journeyman

Points: 96
More actions
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

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

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

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

Hi

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

Clear Sky SQL
My Blog[/url]

Paul White SSC Guru Points: 150467 More actions · Answer 2

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

asashank82 SSC Journeyman Points: 96 More actions · Answer 3

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.

Paul White SSC Guru Points: 150467 More actions · Answer 4

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

asashank82 SSC Journeyman Points: 96 More actions · Answer 5

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.

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 6

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

Clear Sky SQL
My Blog[/url]