在sql server中分割字符串 [英] Splitting the string in sql server
本文介绍了在sql server中分割字符串的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在数据库中有一个字符串,以逗号分隔。喜欢
'苹果,香蕉,菠萝,葡萄'
我需要在逗号的基础上拆分这个字符串并迭代通过这。没有内置函数在sql server,有没有任何有效的方式,其中这个目标可以实现。
I have a string in the database which is comma separated.Like 'apple,banana,pineapple,grapes' I need to split this string on the basis of comma and iterate through this.Since there is no built in function in sql server, Is there any efficient way in which this objective can be attained.
推荐答案
函数
CREATE FUNCTION [dbo].[func_Split]
(
@DelimitedString varchar(8000),
@Delimiter varchar(100)
)
RETURNS @tblArray TABLE
(
ElementID int IDENTITY(1,1), -- Array index
Element varchar(1000) -- Array element contents
)
AS
BEGIN
-- Local Variable Declarations
-- ---------------------------
DECLARE @Index smallint,
@Start smallint,
@DelSize smallint
SET @DelSize = LEN(@Delimiter)
-- Loop through source string and add elements to destination table array
-- ----------------------------------------------------------------------
WHILE LEN(@DelimitedString) > 0
BEGIN
SET @Index = CHARINDEX(@Delimiter, @DelimitedString)
IF @Index = 0
BEGIN
INSERT INTO
@tblArray
(Element)
VALUES
(LTRIM(RTRIM(@DelimitedString)))
BREAK
END
ELSE
BEGIN
INSERT INTO
@tblArray
(Element)
VALUES
(LTRIM(RTRIM(SUBSTRING(@DelimitedString, 1,@Index - 1))))
SET @Start = @Index + @DelSize
SET @DelimitedString = SUBSTRING(@DelimitedString, @Start , LEN(@DelimitedString) - @Start + 1)
END
END
RETURN
END
使用示例 - 只需传递函数逗号分隔字符串以及必需的分隔符。
Example Usage – simply pass the function the comma delimited string as well as your required delimiter.
DECLARE @SQLStr varchar(100)
SELECT @SQLStr = 'Mickey Mouse, Goofy, Donald Duck, Pluto, Minnie Mouse'
SELECT
*
FROM
dbo.func_split(@SQLStr, ',')
$ b b
结果将是这样
Result will be like this
这篇关于在sql server中分割字符串的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文