how tp split data in a table in database

Question

0.00/5 (No votes)

See more:

hi i have a table in some database as follows

1,sam,IT,Engineer,10000|2,john,IT,Lead,20000|3,abraham,IT,Engineer,10000
1,sam,IT,Engineer,10000|2,john,IT,Lead,20000|3,abraham,IT,Engineer,10000
these are under one column

i have inserted this values using the following code

C#

protected void Button1_Click(object sender, EventArgs e)
{
string file=@"C:\Documents and Settings\izazahmed.s\My Documents\sample.txt";
StreamReader sr = new StreamReader(file);
if (!string.IsNullOrEmpty(file))
{
    string[] FileData = File.ReadAllLines(file);
    StringBuilder sb = new StringBuilder();
    foreach (string Data in FileData)
    {
        sb.Append(Data);
        sb.Append('|');
    }
    SqlConnection con = new SqlConnection("Data Source=I20262;Initial Catalog=TRAIN;User ID=train;Password=train");
    SqlCommand cmd = new SqlCommand("txtsbproc", con);
    cmd.CommandType = CommandType.StoredProcedure;
    con.Open();
    cmd.Parameters.AddWithValue("@sv", sb.ToString());
    cmd.ExecuteNonQuery();
    con.Close();
    Label1.Visible = true;
    Label1.Text = "values are inserted";

}

now my task is that how can i split data in table separated by pipe('|') symbol
i shoul get like as follows

1,sam,IT,Engineer,10000
2,john,IT,Lead,20000
3,abraham,IT,Engineer,10000 in the table

Posted 12-Feb-13 17:43pm

Member 9818966

Updated 12-Feb-13 17:45pm

Karthik Harve

v2

Add a Solution

Comments

Karthik Harve 12-Feb-13 23:45pm

[Edit] added pre tags.

Karthik Harve 12-Feb-13 23:48pm

What is a difficulty ? you can split on the symbol, apply for loop on it and insert into DataTable.

chaau 13-Feb-13 1:09am

Why didn't you split the data in your code? Would have been much easier

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

S. M. Ahasan Habib · Answer 1 · 2013-02-14T22:01:00

First you need to create a table valued function like as follows

SQL

CREATE FUNCTION  dbo.SplitString
(
	@stringToSplit VARCHAR(MAX)
)
RETURNS @returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN
	DECLARE @name NVARCHAR(255)
	DECLARE @pos INT
	
	WHILE CHARINDEX('|', @stringToSplit) > 0
	BEGIN
	    SELECT @pos = CHARINDEX('|', @stringToSplit)  
	    SELECT @name = SUBSTRING(@stringToSplit, 1, @pos -1)
	    
	    INSERT INTO @returnList
	    SELECT @name
	    
	    SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos + 1, LEN(@stringToSplit) -@pos)
	END
	
	INSERT INTO @returnList
	SELECT @stringToSplit
	
	RETURN
END

you can test this method as follows

select * from dbo.SplitString('1,sam,IT,Engineer,10000|2,john,IT,Lead,20000|3,abraham,IT,Engineer,10000');

it will return:

1,sam,IT,Engineer,10000
2,john,IT,Lead,20000
3,abraham,IT,Engineer,10000

You can not use this method as like

select * from [dbo].[SplitString]((select data from temporary1));

but it will works

select * from [dbo].[SplitString]((select top 1 data from temporary1));

But why?
If you look carefully the method is accept a single string value. So if you need to use this with any table then you should create a procedure which itereate row by row in your table and pick row and send it to the function. Then function will split string into multiple rows. You just concate/insert/union all rows and use that.

Solution:
you can use cross apply.

SQL

select * from temporary1
cross apply [dbo].[SplitString](data);