Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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 1-Jan-13 5:30am
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Create a Funciton that convert comma seperated values to independent values.
 
Below is the function
 
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
 
 
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.
  Permalink  
Comments
Atul Rokade at 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
Sheikh Muhammad Haris at 2-Jan-13 7:44am
   
The function returns a DataTable. You can bind it with your listbox.
Atul Rokade at 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 at 2-Jan-13 10:01am
   
ok sir thank you very much
Sheikh Muhammad Haris at 3-Jan-13 11:48am
   
Check my solution below
Atul Rokade at 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 .. :)
Sheikh Muhammad Haris at 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 at 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
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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.
 
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)
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 7,903
1 Sergey Alexandrovich Kryukov 7,192
2 DamithSL 5,604
3 Manas Bhardwaj 4,986
4 Maciej Los 4,820


Advertise | Privacy | Mobile
Web04 | 2.8.1411023.1 | Last Updated 3 Jan 2013
Copyright © CodeProject, 1999-2014
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