从数据库中的值中删除逗号分隔符 [英] remove comma seprator from values in database
本文介绍了从数据库中的值中删除逗号分隔符的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
hi
在我的数据库中,我输入以逗号分隔的值,如小,中,大现在我想在下拉列表中显示这个值分隔
small
中等
大
我该怎么办
解决方案
尝试这个简单的功能
IF OBJECT_ID (' fnSplit') IS NOT NULL DROP 功能 dbo.fnSplit
GO
CREATE FUNCTION dbo.fnSplit(
@ InputString VARCHAR ( 8000 ), - 分隔项目清单
@Delimiter CHAR ( 1 )= ' ,') - 分隔项目的分隔符)
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 , @ InputS tring )
END
返回
END
GO
SELECT ColumnValues FROM fnSplit(' Micro Soft,SQL Server 2008 ,R2',' ,')
问候,
GVPrabu
使用此功能分割
ALTER 功能 [dbo]。[CM_Split ](
@ sInputList VARCHAR ( 8000 ) - 分隔项目清单
, @sDelimiter VARCHAR ( 8000 )= ' ,' - 分隔项目的分隔符
) RETURNS @ List 表(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 < span class =code-sdkkeyword> @ List SELECT @ sItem
END
IF LEN( @sInputList )> 0
INSERT INTO < span class =code-sdkkeyword> @ List SELECT @sInputList - 将最后一项放在
RETURN
END
GO
hi,
创建以下功能
CREATE FUNCTION [dbo]。[String_Tokenizer]
(
@ RowData nvarchar (max),
@ SplitOn nvarchar ( 5 )
)
RETURNS@ RtnValue table
(
数据 nvarchar ( 100 )
)
AS
BEGIN
声明 @ Cnt int
设置 @ Cnt = 1
while (Charindex( @SplitOn , @ RowData )> 0)
开始
插入 进入 @ RtnValue (数据)
选择
Data = ltrim(rtrim(Substring( @ Ro) wData , 1 ,Charindex( @ SplitOn , @RowData ) - 1)))
设置 @ RowData = Substring( @ RowData ,Charindex( @ SplitOn , @RowData )+ 1,len( @ RowData ))
设置 @ Cnt = @ Cnt + 1
结束
插入 进入 @ RtnValue (数据)
选择数据= ltrim(rtrim( @ RowData ))
返回
< span class =code-keyword> END
此函数将返回一个表变量。
示例代码选择 * 来自 [dbo]。[String_Tokenizer](' 1,2,3,4',' ,')
输出:
数据
1
2
3
4 跨度>
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
解决方案
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
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
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 codeselect * from [dbo].[String_Tokenizer]('1,2,3,4',',')
Output:
Data 1 2 3 4
这篇关于从数据库中的值中删除逗号分隔符的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文