[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
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值