tp如何在数据库中的表中拆分数据 [英] how tp split data in a table in database

问题描述

嗨我在一些数据库中有一张表如下



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

这些是在一列之下



i使用以下代码插入此值

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";

}

现在我的任务是如何将表中的数据拆分为由管道分隔（'' |''）符号

i shoul如下所示



1，sam，IT，Engineer，10000

2，john，IT，Lead，20000

3，abraham，IT，Engineer，10000 in the table

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

推荐答案

首先你需要创建一个表值函数，如下所示

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);

这篇关于tp如何在数据库中的表中拆分数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！