SQL字串截取函数编写及应用

34rfr

SQL里面一种用来截取字符串的方法，用的是表函数实现字符串截取并应用的SQL操作语句中。
1、截取字符串表函数

ALTER FUNCTION [dbo].[SplitToTable]
(
    -- Add the parameters for the function here
    @SplitString nvarchar(max),
    @Separator nvarchar(10)=''
)
RETURNS
@SplitStringsTable TABLE
(
    -- Add the column definitions for the TABLE variable here
    [id] int identity(1,1),
    [value] nvarchar(max)
)
AS
BEGIN
    -- Fill the table variable with the rows for your result set
    DECLARE @CurrentIndex int;
    DECLARE @NextIndex int;
    DECLARE @ReturnText nvarchar(max);
    SELECT @CurrentIndex=1;
    WHILE(@CurrentIndex<=len(@SplitString))
        BEGIN
            SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex);
            IF(@NextIndex=0 OR @NextIndex IS NULL)
                SELECT @NextIndex=len(@SplitString)+1;
                SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex);
                INSERT INTO @SplitStringsTable([value]) VALUES(@ReturnText);
                SELECT @CurrentIndex=@NextIndex+1;
            END
    RETURN
END

2、应用表函数实现一次多语句添加数据

ALTER PROCEDURE [dbo].[A_AccountPhotoInsert]
    -- Add the parameters for the stored procedure here
    @accid int,
    @accphoto nvarchar(max),
    @albumname nvarchar(50)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    DECLARE @Currentid int; --切割字符串表值函数的ID
    DECLARE @Excisionurl nvarchar(50); --字符串切割后的值，即要删除的ID
    SET @Currentid=1;--初始切割字符串表值函数的ID
WHILE (@Currentid <= (SELECT COUNT(*) FROM SplitToTable(@accphoto,'|')))
        BEGIN
            SELECT @Excisionurl = (select value from SplitToTable(@accphoto,'|') WHERE id = @Currentid)
            insert into A_AccountPhone ([Accountid],[AccountPhoto],[AlbumName],[photoName],[BlockNone],[Headphoto])
            values(@accid,@Excisionurl,@albumname,'',0,0)
            SELECT @Currentid = @Currentid + 1;
        END
    SELECT @Currentid
END

3、应用表函数实现一次多表数据的删除

ALTER PROCEDURE [dbo].[A_AccountDelete]
    -- Add the parameters for the stored procedure here
    @StrId nvarchar(max)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    DECLARE @Currentid int; --切割字符串表值函数的ID
    DECLARE @Excisionid int; --字符串切割后的值，即要删除的ID
    SET @Currentid=1;--初始切割字符串表值函数的ID

    WHILE (@Currentid <= (SELECT COUNT(*) FROM SplitToTable(@StrId,',')))
        BEGIN
SET XACT_ABORT on
BEGIN TRAN
            SELECT @Excisionid = (select value from SplitToTable(@StrId,',') WHERE id = @Currentid)
            DELETE FROM U_AccountInfo WHERE AccountID = @Excisionid
            DELETE FROM UserAccount WHERE id = @Excisionid
            SELECT @Currentid = @Currentid + 1;
COMMIT TRAN
        END
    SELECT @Currentid
END