Click here to Skip to main content
12,451,425 members (49,633 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: SQL-Server
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
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
Updated 12-Feb-13 17:45pm
v2
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.
Andrew Cherednik 13-Feb-13 1:09am
   
Why didn't you split the data in your code? Would have been much easier

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

First you need to create a table valued function like as follows
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.
select * from temporary1
cross apply [dbo].[SplitString](data);
  Permalink  
v3

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.160826.1 | Last Updated 15 Feb 2013
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100