Click here to Skip to main content
11,493,171 members (70,547 online)
Rate this: bad
good
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 18:43pm
Edited 12-Feb-13 18:45pm
v2
Comments
Karthik Harve at 12-Feb-13 23:45pm
   
[Edit] added pre tags.
Karthik Harve at 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 at 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
0 Sergey Alexandrovich Kryukov 419
1 Maciej Los 286
2 F-ES Sitecore 224
3 KarstenK 210
4 CPallini 208
0 Sergey Alexandrovich Kryukov 10,132
1 OriginalGriff 8,630
2 Sascha Lefèvre 3,624
3 Maciej Los 3,392
4 Richard Deeming 2,500


Advertise | Privacy | Mobile
Web02 | 2.8.150520.1 | Last Updated 15 Feb 2013
Copyright © CodeProject, 1999-2015
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