字符串分割大全及实例

1. 分割后按顺序取数据

/* ---------------------------------------------------
fnMain_GetStrSegment (SQL_SCALAR_FUNCTION)
Description: Extracts a specific segment of a string, given the delimiter
Usage remarks / example:
   e.g. Input: 'A•B•C'   Delimiter: '•'
     A = Segment 1
     B = Segment 2
     C = Segment 3
     SELECT dbo.fnMain_GetStrSegment('A•B•C','•',2); --This should return 'B'
---------------------------------------------------*/
BEGIN TRY DROP FUNCTION [dbo].[fnMain_GetStrSegment] END TRY BEGIN CATCH END CATCH
GO
CREATE FUNCTION [dbo].[fnMain_GetStrSegment](@InputStr nvarchar(max), @Delimiter nvarchar(255), @SegmentNo int) RETURNS nvarchar(max) AS
BEGIN
    DECLARE @Result nvarchar(max),@StartPos int,@EndPos int,@Cnt int,@DelimLen tinyint;
    SELECT @Result='',@Cnt=1,@DelimLen=CASE WHEN @Delimiter=N' ' THEN 1 ELSE LEN(@Delimiter) END;
    WHILE @Cnt<=@SegmentNo
    BEGIN
        IF @Cnt=1
        BEGIN SET @StartPos=1 END
        ELSE BEGIN
            SET @StartPos=ISNULL(CHARINDEX(@Delimiter,@InputStr,@StartPos),0)+@DelimLen;
            IF @StartPos<=1 RETURN '';
        END
        SET @EndPos=ISNULL(CHARINDEX(@Delimiter,@InputStr,@StartPos),0);
        IF @EndPos<@StartPos SET @EndPos=LEN(@InputStr)+@DelimLen;
        SET @Cnt=@Cnt+1;
    END
    IF @StartPos>0 AND @EndPos>@StartPos SET @Result=SUBSTRING(@InputStr,@StartPos,@EndPos-@StartPos);
    RETURN @Result;
END
GO

 

-------For Example     --SELECT dbo.fnMain_GetStrSegment('1|4|5',N'|',2)

 

2.  分割成整形表

/* ---------------------------------------------------
tfMain_StrToTblInt (SQL_TABLE_VALUED_FUNCTION)
Description: Function to convert a string into a table of integers by splitting with a given delimiter
Usage remarks / example:
---------------------------------------------------*/
BEGIN TRY DROP FUNCTION [dbo].[tfMain_StrToTblInt] END TRY BEGIN CATCH END CATCH
GO
CREATE FUNCTION [dbo].[tfMain_StrToTblInt](@InputStr nvarchar(MAX), @Delimiter nchar(1)) RETURNS @OutputTable TABLE ( Val int ) AS
BEGIN
    DECLARE @TmpVal nvarchar(max);
    WHILE LEN(@InputStr) > 0
    BEGIN
        SET @TmpVal = LTRIM(RTRIM(LEFT(@InputStr, ISNULL(NULLIF(CHARINDEX(@Delimiter, @InputStr) - 1, -1), LEN(@InputStr)))));
        SET @InputStr = SUBSTRING(@InputStr, ISNULL(NULLIF(CHARINDEX(@Delimiter, @InputStr), 0), LEN(@InputStr)) + 1, LEN(@InputStr));
        IF LEN(@TmpVal)>0 AND ISNUMERIC(@TmpVal)<>0
        BEGIN
            INSERT INTO @OutputTable ( Val ) VALUES ( CONVERT(int,@TmpVal) );
        END
    END
    RETURN;
END
GO
BEGIN TRY DROP FUNCTION [dbo].[tfMain_StrToTblInt_Ordered] END TRY BEGIN CATCH END CATCH
GO
CREATE FUNCTION [dbo].[tfMain_StrToTblInt_Ordered](@InputStr nvarchar(MAX), @Delimiter nchar(1)) RETURNS @OutputTable TABLE ( RowNo int PRIMARY KEY, Val int ) AS
BEGIN
    DECLARE @TmpVal nvarchar(max), @RowNo int; SET @RowNo=1;
    WHILE LEN(@InputStr) > 0
    BEGIN
        SET @TmpVal = LTRIM(RTRIM(LEFT(@InputStr, ISNULL(NULLIF(CHARINDEX(@Delimiter, @InputStr) - 1, -1), LEN(@InputStr)))));
        SET @InputStr = SUBSTRING(@InputStr, ISNULL(NULLIF(CHARINDEX(@Delimiter, @InputStr), 0), LEN(@InputStr)) + 1, LEN(@InputStr));
        IF LEN(@TmpVal)>0 AND ISNUMERIC(@TmpVal)<>0
        BEGIN
            INSERT INTO @OutputTable ( RowNo, Val ) VALUES ( @RowNo, CONVERT(int,@TmpVal) );
            IF @@ROWCOUNT>0 SET @RowNo=@RowNo+1;
        END
    END
    RETURN;
END
GO

-------For Example     --select val from dbo.[tfMain_StrToTblInt_Ordered]('7•4•5','•')

 

2.  分割成字符表

 

/* ---------------------------------------------------
tfMain_StrToTblStr (SQL_TABLE_VALUED_FUNCTION)
Description: Function to convert a string into a table of strings by splitting with a given delimiter
Usage remarks / example:
---------------------------------------------------*/
BEGIN TRY DROP FUNCTION [dbo].[tfMain_StrToTblStr] END TRY BEGIN CATCH END CATCH
GO
CREATE FUNCTION [dbo].[tfMain_StrToTblStr](@InputStr nvarchar(MAX), @Delimiter nchar(1)) RETURNS @OutputTable TABLE ( Val nvarchar(MAX) ) AS
BEGIN
    DECLARE @TmpVal nvarchar(max);
    WHILE LEN(@InputStr) > 0
    BEGIN
        SET @TmpVal = LEFT(@InputStr, ISNULL(NULLIF(CHARINDEX(@Delimiter, @InputStr) - 1, -1), LEN(@InputStr)));
        SET @InputStr = SUBSTRING(@InputStr, ISNULL(NULLIF(CHARINDEX(@Delimiter, @InputStr), 0), LEN(@InputStr)) + 1, LEN(@InputStr));
        INSERT INTO @OutputTable ( Val ) VALUES ( @TmpVal );
    END
    RETURN;
END
GO
BEGIN TRY DROP FUNCTION [dbo].[tfMain_StrToTblStr_Ordered] END TRY BEGIN CATCH END CATCH
GO
CREATE FUNCTION [dbo].[tfMain_StrToTblStr_Ordered](@InputStr nvarchar(MAX), @Delimiter nchar(1)) RETURNS @OutputTable TABLE ( RowNo int PRIMARY KEY, Val nvarchar(MAX) ) AS
BEGIN
    DECLARE @TmpVal nvarchar(max), @RowNo int; SET @RowNo=1;
    WHILE LEN(@InputStr) > 0
    BEGIN
        SET @TmpVal = LEFT(@InputStr, ISNULL(NULLIF(CHARINDEX(@Delimiter, @InputStr) - 1, -1), LEN(@InputStr)));
        SET @InputStr = SUBSTRING(@InputStr, ISNULL(NULLIF(CHARINDEX(@Delimiter, @InputStr), 0), LEN(@InputStr)) + 1, LEN(@InputStr));
        INSERT INTO @OutputTable ( RowNo, Val ) VALUES ( @RowNo, @TmpVal );
        IF @@ROWCOUNT>0 SET @RowNo=@RowNo+1;
    END
    RETURN;
END
GO

 

-------For Example     --select val from dbo.tfMain_StrToTblStr('1•4•5','•')