Click here to Skip to main content
11,802,442 members (71,833 online)
Rate this: bad
Please Sign up or sign in to vote.
See more: SQL-server-2005 SQL-Server , +
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 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
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)
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
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
0 KrunalRohit 366
1 OriginalGriff 365
2 F-ES Sitecore 295
3 Afzaal Ahmad Zeeshan 230
4 CPallini 230
0 OriginalGriff 2,950
1 Maciej Los 1,910
2 KrunalRohit 1,862
3 CPallini 1,645
4 Richard MacCutchan 1,157

Advertise | Privacy | Mobile
Web03 | 2.8.151002.1 | Last Updated 3 Jan 2013
Copyright © CodeProject, 1999-2015
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