Click here to Skip to main content
15,077,641 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
iam creating one application where i use list box which have multiple value user can select multiple listbox value ,value store in comma separated type now i want to retrieve this value in database how can i do that?
Posted

Create a Funciton that convert comma seperated values to independent values.

Below is the function

SQL
CREATE Function [dbo].[FN_CsvToInt] ( @Array varchar(3000))       
returns @IntTable table       
 (IntValue varchar(25))      
AS      
begin      
      
 declare @separator char(1)      
 set @separator = ','      
      
 declare @separator_position int       
 declare @array_value varchar(3000)       
       
 set @array = @array + ','      
       
 while patindex('%,%' , @array) <> 0       
 begin      
       
   select @separator_position =  patindex('%,%' , @array)      
   select @array_value = left(@array, @separator_position - 1)      
       
  Insert @IntTable      
  Values (Cast(@array_value as varchar(25)))      
      
   select @array = stuff(@array, 1, @separator_position, '')      
 end      
      
 return      
end      


To select the values, write a query like this

SQL
select * from [FN_CsvToInt]('1,564,654,98,654,321,32,13,21,49') -- use your column name here as parameter 



Accept Solution if it helps you.
   
Comments
Atul Rokade 2-Jan-13 2:30am
   
thank you very much sir for your valuable reply, but i want to show that value as selected in listbox in ado.net
[no name] 2-Jan-13 7:44am
   
The function returns a DataTable. You can bind it with your listbox.
Atul Rokade 3-Jan-13 11:42am
   
sheikh sir i run above query it run fine but select * from [FN_CsvToInt]('1,564,654,98,654,321,32,13,21,49') here when i use my column name it give me only column name not that there details like i wrote select * from [FN_CsvToInt]('Electro_Therapy') so it give me as out put Electro_Therapy only n that is column name not there items pls help me sir
Atul Rokade 2-Jan-13 10:01am
   
ok sir thank you very much
[no name] 3-Jan-13 11:48am
   
Check my solution below
Atul Rokade 4-Jan-13 0:43am
   
sheikh sir thank u very much for ur great support but im used ur solution in my editor n pass my column name but it give me Invalid column name 'Electro_Therapy' , "Electro_Therapy" is my column name

DECLARE @T TABLE (data VARCHAR(50), RegisterNo INT)
---------------
INSERT INTO @T
SELECT 'Abc,xyz', 1

--select * from [FN_CsvToInt]('1,2,3,4,5,6,7,8,9')

declare @values varchar(50)
select @values = data from @T where RegisterNo = 1

select Electro_Therapy from [FN_CsvToInt](@values)
its your query .. :)
[no name] 4-Jan-13 11:11am
   
@T is just a temporary table my dear :) Use your table name instead. try this.


declare @values varchar(50)
select @values = Electro_Therapy from [Your_Table_Name] where [Your_Table_PK_ID] = 1

select wd_code from [FN_CsvToInt](@values)
Atul Rokade 4-Jan-13 14:38pm
   
ok sheikh sir now my all doubt clear really thnk u very much ur support thn a lot n wish u the happy,healthy,wealthy,enjoyful new year god bless u
Since its a Table valued Function, that returns a table in an output. you have to use variable for that.

Store your column value in a variable and then use the function to separate values.

SQL
DECLARE @T TABLE (data VARCHAR(50), ID INT)
---------------
INSERT INTO @T
SELECT '1,2,3,4,5,6,7,8,9', 1

--select * from [FN_CsvToInt]('1,2,3,4,5,6,7,8,9')

declare @values varchar(50)
select @values = data from @T where ID = 1

select wd_code from [FN_CsvToInt](@values)
   

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900