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
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))      
 declare @separator char(1)      
 set @separator = ','      
 declare @separator_position int       
 declare @array_value varchar(3000)       
 set @array = @array + ','      
 while patindex('%,%' , @array) <> 0       
   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, '')      

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

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)

    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