remove comma seprator from values in database

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008

, +

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 22:56pm

Member 9815058

Updated 4-Mar-13 22:58pm

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

gvprabu · Answer 1 · 2013-03-04T23:18:00

Hi,

Try This Simple Function

SQL

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

Davidduraisamy · Answer 2 · 2013-03-04T23:00:00

Use this function to split

SQL

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

Shanalal Kasim · Answer 3 · 2013-03-04T23:01:00

hi,
Create below function

SQL

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

SQL

select * from [dbo].[String_Tokenizer]('1,2,3,4',',')

Output:

SQL

Data
1
2
3
4

remove comma seprator from values in database

3 solutions

Solution 3

Solution 1

Solution 2

Add your solution here

Preview 0