How to extract one item from multi item without comma

Question

4.00/5 (1 vote)

See more:

SQL-Server-2005

Hii...

I am working in windows Application and using sql 2005.I have a table called "Details" with having two column named,"No","ServiceName". While I am saving Data from front-end the datas of ServiceName will store in database,separating by comma.Like

No ServiceName
1 Pen,Pencil,Ruler

So i want,when i will retrieve data in a grid,i want to see single item in a single column of a grid.
Like,

No Name
1 Pen
1 Pencil
1 Ruler

So i donot understand how to do this.please help me to solve this.

Posted 20-May-13 0:15am

Shivani Dash

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)

Aarti Meswania · Answer 1 · 2013-05-20T00:27:00

create this function

SQL

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[ParseValues]
(@String varchar(8000), @Delimiter varchar(max) )
RETURNS @RESULTS TABLE (ID int identity(1,1), Val varchar(max))
AS
BEGIN
    DECLARE @Value varchar(max)
    WHILE @String is not null
    BEGIN
        SELECT @Value=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN LEFT(
          @String,PATINDEX('%'+@Delimiter+'%',@String)-1) ELSE @String END,
          @String=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN SUBSTRING(
          @String,PATINDEX('%'+@Delimiter+'%',@String)+LEN(@Delimiter),LEN(@String)) ELSE NULL END
        INSERT INTO @RESULTS (Val)
        SELECT @Value
    END
RETURN
END

Now, use this query

SQL

select No,b.val as servicename 
from yourtable
cross apply (dbo.PasrseValues(servicename,',')) as b
Where b.val <> ''

Happy coding!
:)

Basmeh Awad · Answer 2 · 2013-05-20T00:34:00

after getting data in datareader you have to split it using split function

VB

Dim items() As String
Dim i As Integer
dim str as String
str = dr(0) 'your datareader
items = str.Split(",")
For Each item As String In items
    DataGridView1.Item(1, i).value = items(i) 'give your column index and row index
Next

and you havent taged in which language you are working vb.net or C#

for C#

C#

string[] items = null;
String str;
int i = 0;
str = dr[0];
items = str().Split(",");
foreach (string item in items) {
	DataGridView1.Item(1, i).value = items[i];
}

How to extract one item from multi item without comma

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0