Click here to Skip to main content
12,951,710 members (66,334 online)
Rate this:
 
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 4: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 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 2-Jan-13 7:44am
   
The function returns a DataTable. You can bind it with your listbox.
Atul Rokade 2-Jan-13 10:01am
   
ok sir thank you very much
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
Sheikh Muhammad Haris 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 .. :)
Sheikh Muhammad Haris 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
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
Top Experts
Last 24hrsThis month
OriginalGriff 6,229
CHill60 3,490
Maciej Los 3,083
Jochen Arndt 1,975
ppolymorphe 1,880


Advertise | Privacy | Mobile
Web02 | 2.8.170525.1 | Last Updated 3 Jan 2013
Copyright © CodeProject, 1999-2017
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