在 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, ',')
结果会是这样
相关文章