[LzmTW].[String].[IndexArray]

Author:水如烟  

USE   [ LzmTW ]
GO
--  ================================================================================
--  Author:LzmTW
--  Create date:20080110
--  Description:返回子字符串在给定字符串中的匹配索引的集合(仅取最紧凑的匹配项)
--  @IsVagueMode:是否在使用模糊查询方式 0否,1是
--  Note:模糊查询中对单%无效，即@Search = N'%%%'时无效
--  ================================================================================
CREATE   FUNCTION   [ String ] . [ IndexArray ]  
(
      @Value   nvarchar ( max )
    , @Search   nvarchar ( max )
    , @IsVagueMode   bit   =   0
)
RETURNS  
@Table   TABLE  
(
      [ ID ]   int   identity ( 1 , 1 )  PRIMARY   KEY
    , [ Index ]   int
    , [ Catch ]   nvarchar ( max )
    , [ Len ]   int
)
AS
BEGIN
     IF   @Value   IS   NULL   OR   @Search   IS   NULL   OR   @IsVagueMode   IS   NULL
         RETURN

     DECLARE
          @String   nvarchar ( max ) 
        , @Pattern   nvarchar ( max )
        , @Index   int
        , @CurrentIndex   int

        , @Catch   nvarchar ( max )
        , @Tmp   nvarchar ( max )
        , @TmpLen   int
        , @TmpIndex   int

     SET   @String   =   @Value

     IF   @IsVagueMode   =   0
     BEGIN
         SET   @Pattern   =   @Search

         SET   @Index   =   CHARINDEX ( @Pattern ,  @String )
         SET   @CurrentIndex   =   0
         SET   @Catch   =   @Pattern

         WHILE   @Index   >   0
         BEGIN
             INSERT   INTO   @Table   VALUES  ( @Index ,  @Catch ,  [ String ] . [ Len ] ( @Catch ))

             SET   @CurrentIndex   =   @Index   +   1

             SET   @Index   =   CHARINDEX ( @Pattern ,  @String ,  @CurrentIndex )
         END
     END
     ELSE
     BEGIN
         SET   @Pattern   =   @Search

         SET   @Index   =   PATINDEX ( @Pattern ,  @String )
         SET   @CurrentIndex   =   0

         WHILE   @Index   >   0
         BEGIN
             SET   @CurrentIndex   =   @CurrentIndex   +   @Index

             SET   @Tmp   =   @String

             SET   @TmpLen   =   LEN ( @Tmp )
             SET   @TmpIndex   =   0

             WHILE   PATINDEX ( @Pattern ,  @Tmp )  >   0   AND   @TmpLen   >=   @TmpIndex
             BEGIN
                 SET   @Catch   =   @Tmp

                 SET   @TmpIndex   =   @TmpIndex   +   1
                 IF   @TmpLen   >   =   @TmpIndex
                     SET   @Tmp   =   LEFT ( @Tmp ,  @TmpLen   -   @TmpIndex )
             END

             SET   @Tmp   =   @Catch
             SET   @TmpLen   =   LEN ( @Tmp )
             SET   @TmpIndex   =   0

             WHILE   PATINDEX ( @Pattern ,  @Tmp )  >   0   AND   @TmpLen   >=   @TmpIndex
             BEGIN
                 SET   @Catch   =   @Tmp

                 SET   @TmpIndex   =   @TmpIndex   +   1
                 IF   @TmpLen   >   =   @TmpIndex
                     SET   @Tmp   =   RIGHT ( @Tmp ,  @TmpLen   -   @TmpIndex )
             END
        
             IF   SUBSTRING ( @Value ,  @CurrentIndex ,  [ String ] . [ Len ] ( @Catch ))  =   @Catch
                 INSERT   INTO   @Table   VALUES  ( @CurrentIndex ,  @Catch ,  [ String ] . [ Len ] ( @Catch ))

             SET   @String   =   STUFF (  @String ,  1 ,  @Index , N '' )
             SET   @Index   =   PATINDEX ( @Pattern ,  @String )
         END     
     END

     RETURN  
END