Split a string in sql 2008

Question

0.00/5 (No votes)

See more:

C#

ASP.NET

How can i split a string from database field value....

Posted 27-Feb-13 17:56pm

Nik's Sangani

Add a Solution

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

Tejas Vaishnav · Accepted Answer · 2013-02-27T18:09:00

Solution 2

Please review this links....

http://blog.logiclabz.com/sql-server/split-function-in-sql-server-to-break-comma-separated-strings-into-table.aspx[^]

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648[^]

Posted 27-Feb-13 18:09pm

Tejas Vaishnav

Comments

Nik's Sangani 28-Feb-13 0:13am

but i want to use table field... in that i have a string,...
select top 10 * from dbo.split('Chennai,Bangalore,Mumbai',',')
i want to use field name not a string like='abc,def,fgh'

_Amy · Accepted Answer · 2013-02-27T18:06:00

You can create a function to split the strings. Try the following function:

SQL

CREATE FUNCTION dbo.Split
(
	@RowData nvarchar(2000),
	@SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(
	Id int identity(1,1),
	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

Original Source : Here[^].

--Amit