在SQL Server 2008中更新以逗号分隔的字符串 [英] Update Comma separated string in SQL Server 2008

问题描述

我在前端有一个Gridview，Grid有两列：ID和Order就像这样：

Hi,

I have a Gridview in front end where Grid have two columns : ID and Order like this:

ID            Order

   1               2
   2               4
   3               1
   4               3

订单栏是可编辑的。现在，如果我想更新订单并进行保存，我想将其存储到数据库中。我已将ID和Order存储为逗号分隔的字符串，如sID（1,2,3,4）和sOrder（2,4,1,3），并作为输入参数发送到SQL Server。通过存储过程如何更新到表中。

Order column is editable. Now if I want to update the order and make save I want to store it into database. I have stored ID and Order as a comma separated string like sID(1,2,3,4) and sOrder(2,4,1,3) and sent to SQL Server as input parameters. Through Stored procedure how can update into the table.

推荐答案

使用tokenize函数分隔id和order（使用下面的函数）。

这个函数返回表变量。使用游标，从表变量中获取行并更新主表。

Separate ids and order using tokenize function(use below function).
This function return table variable. Using cursor, fetch rows from table variable and update main table.

CREATE FUNCTION [dbo].[String_Tokenizer]
(
	@RowData nvarchar(max),
	@SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(
	Data nvarchar(100)
) 
AS  
BEGIN 
	Declare @Cnt int
	Set @Cnt = 1
 
	While (Charindex(@SplitOn,@RowData)>0)
	Begin
		Insert Into @RtnValue (data)
		Select 
			Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))
 
		Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
		Set @Cnt = @Cnt + 1
	End
	
	Insert Into @RtnValue (data)
	Select Data = ltrim(rtrim(@RowData))
 
	Return
END

创建此功能

create this function

CREATE Function [dbo].[fn_Splitter] (@IDs Varchar(100))  
Returns @Tbl_IDs Table (ID Int)  As  
Begin 
 -- Append comma
 Set @IDs =  @IDs + ',' 
 -- Indexes to keep the position of searching
 Declare @Pos1 Int
 Declare @pos2 Int
  -- Start from first character 
 Set @Pos1=1
 Set @Pos2=1
 While @Pos1<len(@ids)>
 Begin
  Set @Pos1 = CharIndex(',',@IDs,@Pos1)
  Insert @Tbl_IDs Select  Cast(Substring(@IDs,@Pos2,@Pos1-@Pos2) As Int)
  -- Go to next non comma character
  Set @Pos2=@Pos1+1
  -- Search from the next charcater
  Set @Pos1 = @Pos1+1
 End 
 Return  
 End

现在尝试此查询

now try this query

with tbl_Ids
(
    select row_number() over(partition by Id) as srno,Id 
    from dbo.fn_splitter('1,2,3,4') as tbl_Id
)

select tbl_Ids.Id,tbl_Orders.Order from tbl_Ids
left join
(
    select row_number() over(partition by Id) as srno,Id as Order 
    from dbo.fn_splitter('2,4,1,3') as tbl_Order
) as tbl_Orders on tbl_Ids.srno=tbl_Order.srno

快乐编码！

：）

Happy Coding!
:)

这篇关于在SQL Server 2008中更新以逗号分隔的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！