Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
hi
 
In my database i enter values separated by comma like small,medium,large now i want this value seprate in dropdownlist
small
medium
large
 
how can i do it
Posted 4-Mar-13 23:56pm
Edited 4-Mar-13 23:58pm
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

Hi,
 
Try This Simple Function
 
 
IF OBJECT_ID('fnSplit') IS NOT NULL DROP FUNCTION dbo.fnSplit
GO
CREATE FUNCTION dbo.fnSplit(
    @InputString VARCHAR(8000), -- List of delimited items
    @Delimiter CHAR(1) = ',') -- delimiter that separates items)
RETURNS @List TABLE (ColumnValues VARCHAR(8000))
AS
BEGIN
    DECLARE @NextString NVARCHAR(40), @Pos INT
 
    SET @InputString = @InputString + @Delimiter
    SET @Pos = CHARINDEX(@Delimiter,@InputString)
 
    WHILE (@pos <> 0)
    BEGIN
	   SET @NextString = SUBSTRING(@InputString,1,@Pos - 1)
	   INSERT INTO @List(ColumnValues) SELECT @NextString 
	   SET @InputString = SUBSTRING(@InputString,@pos+1,len(@InputString))
	   SET @pos = CHARINDEX(@Delimiter,@InputString)
    END 
    RETURN 
END
GO
SELECT ColumnValues FROM fnSplit('Micro Soft,SQL Server 2008, R2',',')
 
Regards,
GVPrabu
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Use this function to split
 
ALTER FUNCTION [dbo].[CM_Split](
    @sInputList VARCHAR(8000) -- List of delimited items
  , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))
 
BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
 
 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem
 END
 
IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END
 
GO
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

hi,
Create below function
 
CREATE FUNCTION [dbo].[String_Tokenizer]
(
	@RowData nvarchar(max),
	@SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(
	Data nvarchar(100)
) 
AS  
BEGIN 
	Declare @Cnt int
	Set @Cnt = 1
 
	While (Charindex(@SplitOn,@RowData)>0)
	Begin
		Insert Into @RtnValue (data)
		Select 
			Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))
 
		Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
		Set @Cnt = @Cnt + 1
	End
	
	Insert Into @RtnValue (data)
	Select Data = ltrim(rtrim(@RowData))
 
	Return
END
 
This function will return a table variable.
 
Sample code
select * from [dbo].[String_Tokenizer]('1,2,3,4',',')
 
Output:
 
Data
1
2
3
4
  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,800
1 Sergey Alexandrovich Kryukov 7,072
2 DamithSL 5,604
3 Manas Bhardwaj 4,986
4 Maciej Los 4,790


Advertise | Privacy | Mobile
Web04 | 2.8.1411023.1 | Last Updated 5 Mar 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