如何从多项目中提取一个项目而不使用逗号 [英] How to extract one item from multi item without comma
问题描述
Hii ...
我正在使用Windows应用程序并使用sql 2005.我有一个名为Details的表,其中有两列名为No ,服务名称。当我从前端保存数据时,ServiceName的数据将存储在数据库中,用逗号分隔。喜欢
否 ServiceName
1钢笔,铅笔,尺子
所以我想,当我将在网格中检索数据时,我想要在网格的单个列中查看单个项目。
赞,
否 名称
1笔
1支铅笔
1标尺
所以我不明白怎么做。请帮我解决这个问题。
创建这个函数
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo]。[ParseValues]
( @String varchar ( 8000 ), @Delimiter varchar (max))
RETURNS @ RESULTS 表(ID int 身份( 1 , 1 ),Val varchar (max))
AS
BEGIN
DECLARE @ Value varchar ( max)
WHILE @ String 不 null
BEGIN
SELECT @ Value = CASE WHEN PATINDEX(' %' + @Delimiter + ' %', @ String )> 0 那么 LEFT (
@ String ,PATINDEX(' %' + @Delimiter + ' %', @ String ) - 1) ELSE @ String END ,
@ String = CASE WH EN PATINDEX(' %' + @Delimiter + ' %', @ String )> 0 那么 SUBSTRING(
@ String ,PATINDEX(' %' + @Delimiter + ' %',< span class =code-sdkkeyword> @ String )+ LEN( @Delimiter ),LEN( @字符串)) ELSE NULL END
INSERT INTO @ RESULTS (Val)
SELECT @ Value
END
返回
结束
现在,使用此查询
选择否,b.val as servicename
来自 yourtable
cross apply (dbo.PasrseValues(servicename, ' ,')) as b
其中 b.val<> ' '
快乐编码!
Dim items()作为 字符串
Dim i 作为 整数
dim str as String
str = dr( 0 )' 您的datareader
items = str.Split( ,)
对于 每个项作为 海峡ing 在项
DataGridView1.Item( 1 ,i).value = items(i)' 给你的列索引和行索引
下一页
你没有用你使用的语言vb.net或C#
for C#
string [] items = 空跨度>;
String str;
int i = 0 ;
str = dr [ 0 ];
items = str()。分割( ,);
foreach ( string item in items){
DataGridView1.Item( 1 ,i)。 value = items [i ]。
}
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.
create this function
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
select No,b.val as servicename from yourtable cross apply (dbo.PasrseValues(servicename,',')) as b Where b.val <> ''
Happy coding!
:)
after getting data in datareader you have to split it using split function
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#
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]; }
这篇关于如何从多项目中提取一个项目而不使用逗号的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!