Update Comma separated string in SQL Server 2008

Question

0.00/5 (No votes)

See more:

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

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.

Posted 3-Feb-13 18:46pm

Supratik De

Updated 3-Feb-13 20:21pm

Aarti Meswania

v2

Add a Solution

Comments

madhuri@mumbai 4-Feb-13 0:58am

you mean to say that,u have sended comma separated string to SP i.e '2,3,4,5' right
so you just take input param ID as varchar in SP and then update it.
like update temp set ID='2,3,4,5'

2 solutions

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)

Shanalal Kasim · Answer 1 · 2013-02-03T19:42:00

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.

SQL

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

Aarti Meswania · Answer 2 · 2013-02-03T20:39:00

create this function

SQL

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

SQL

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!
:)

Update Comma separated string in SQL Server 2008

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0