使用SQL函数[Split]分割字符串,并放入内存表中


今天遇到一个SQL方法的编写,需要使用到 SELECT * FROM USER WHERE ID IN(@uid)
@uid为字符串,但是ID是INT类型,无法编译运行,需要使用下面的函数

 表值函数 所以需要像访问表那样访问

CREATE FUNCTION [dbo].[Split]
(
    @Input varchar(4000), -- 输入字符串
    @Separator varchar(4000) = '.' -- 分隔符
)
RETURNS 
@Result TABLE 
(
    Value varchar(4000)
)
AS
BEGIN

DECLARE @InputLength int;
SET @InputLength = LEN(@Input);
DECLARE @SeparatorLength int;
SET @SeparatorLength = LEN(@Separator);
DECLARE @LastIndex int;
SET @LastIndex = 0;
DECLARE @CurrentIndex int;
SET @CurrentIndex = -1;

WHILE @CurrentIndex != 0
BEGIN
    SET @CurrentIndex = CHARINDEX(@Separator, @Input, @LastIndex );

    IF @CurrentIndex != 0
        INSERT @Result
        (
            Value
        )
        VALUES
        (
            SUBSTRING(@Input, @LastIndex, @CurrentIndex - @LastIndex)
        )
    ELSE
        INSERT @Result
        (
            Value
        )
        VALUES
        (
            SUBSTRING(@Input, @LastIndex, @InputLength + 1 - @LastIndex)
        )

    SET @LastIndex = @CurrentIndex + @SeparatorLength;
END

RETURN 

END

GO

select * from  [dbo].[Split]('12,2,3,4,5,6,7',',')

Value                                                                                                                                                                                                                                                            
-------------------------------------------------------------------------------------
12
2
3
4
5
6
7

(所影响的行数为 7 行)

 drop FUNCTION [dbo].[Split]

--经网友修改后方法(精简版)

ALTER FUNCTION [dbo].[Split]
(
    @Input varchar(4000), -- 输入字符串
    @Separator varchar(4000) = '.' -- 分隔符
)
RETURNS @Result TABLE 
(
    [Value] varchar(4000)
)
AS
BEGIN
DECLARE @i INT
SELECT @i=CHARINDEX(@Separator,@Input)
WHILE  @i>0
    BEGIN
        INSERT @Result VALUES(LEFT(@Input,@i-LEN(@Separator)))
        SELECT @Input=STUFF(@Input,1,@i,''),@i=CHARINDEX(@Separator,@Input)
    END
INSERT @Result VALUES(@Input)
RETURN 
END


拼接SQL字符串字段根据用户的ID,[1|2|3|4|] 返回名字的集合['张三|李四|王武|王麻子|']
CREATE FUNCTION FUN_GetNamesByIds
(
@Ids NVARCHAR(50)
)
RETURNS NVARCHAR(100)
AS
BEGIN
DECLARE @Names NVARCHAR(100) SET @Names=''
SELECT @Names=@Names+RealName+'|' FROM Employee WHERE PKID IN
 (SELECT * FROM Split(@Ids,'|'))
return @Names
END



阅读更多
个人分类: SQL Server
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭