详解如何使用SQL中文分词组件查询表记录

 

   
   

    
    CREATEPROCEDURE 
    
    [
    
    dbo
    
    ]
    
    .
    
    [
    
    Search_Test
    
    ]
    
    
    
    
    @Keyword
    
                    
    
    nvarchar
    
    (
    
    50
    
    )
    
    =
    
    ''
    
    

    
    AS
    
    

    
    BEGIN
    
    

    
    
    SET
    
     NOCOUNT 
    
    ON
    
    

    
    
    DECLARE
    
     
    
    @Condition
    
     
    
    NVARCHAR
    
    (
    
    1000
    
    )
        
    
    SET
    
     
    
    @Condition
    
     
    
    =
    
     
    
    '
    
    1 = 1
    
    '
    
    
    
    
    DECLARE
    
     
    
    @Tables
    
     
    
    VARCHAR
    
    (
    
    100
    
    )
    
    
    SET
    
     
    
    @Tables
    
     
    
    =
    
     
    
    '
    
    [vw_tmp] a 
    
    '
    
    

    
    
    DECLARE
    
     
    
    @Sql
    
     
    
    NVARCHAR
    
    (
    
    4000
    
    )
        
    
    SET
    
     
    
    @Sql
    
     
    
    =
    
     
    
    ''
    
    

    
    
    IF
    
     
    
    @Keyword
    
     
    
    <>
    
    ''
    
    
    
    
    BEGIN
    
    
        
    
    declare
    
     
    
    @Filter
    
     
    
    as
    
     
    
    varchar
    
    (
    
    max
    
    )
        
    
    declare
    
     
    
    @Str
    
     
    
    as
    
     
    
    varchar
    
    (
    
    1000
    
    )
        
    
    if
    
     
    
    right
    
    (
    
    @Keyword
    
    ,
    
    1
    
    )
    
    <>
    
    '
    
    /
    
    '
    
    
            
    
    set
    
     
    
    @Str
    
    =
    
    @Keyword
    
     
    
    '
    
    /
    
    '
    
    
        
    
    else
    
    
            
    
    set
    
     
    
    @Str
    
    =
    
    @Keyword
    
    
        
    
    set
    
     
    
    @Filter
    
    =
    
    '
    
     
    
    '
    
    
        
    
    declare
    
       
    
    @Ch
    
       
    
    as
    
       
    
    varchar
    
    (
    
    100
    
    )   
           
        
    
    while
    
    (
    
    @Str
    
    <>
    
    ''
    
    )   
        
    
    begin
    
       

            
    
    set
    
       
    
    @Ch
    
    =left
    
    (
    
    @Str
    
    ,
    
    charindex
    
    (
    
    '
    
    /
    
    '
    
    ,
    
    @Str
    
    ,
    
    1
    
    )
    
    -
    
    1
    
    )   
            
    
    set
    
       
    
    @Str
    
    =
    
    stuff
    
    (
    
    @Str
    
    ,
    
    1
    
    ,
    
    charindex
    
    (
    
    '
    
    /
    
    '
    
    ,
    
    @Str
    
    ,
    
    1
    
    ),
    
    ''
    
    )   
            
    
    set
    
     
    
    @Filter
    
    =
    
    @Filter
    
     
    
    '
    
     or [product_tag] LIKE 
    
    ''
    
    %
    
    '
    
       
    
    @Ch
    
       
    
    '
    
    %
    
    '''
    
    
        
    
    end
    
    

        
    
    if
    
     
    
    charindex
    
    (
    
    '
    
    /
    
    '
    
    ,
    
    @Keyword
    
    ,
    
    1
    
    )
    
    >
    
    0
    
    
            
    
    set
    
     
    
    @Keyword
    
    =
    
    replace
    
    (
    
    @Keyword
    
    ,
    
    '
    
    /
    
    '
    
    ,
    
    ''
    
    )
        
    
    SET
    
     
    
    @Condition
    
     
    
    =
    
     
    
    '
    
      (a.[product_tag]  LIKE 
    
    ''
    
    %
    
    '
    
       
    
    @Keyword
    
       
    
    '
    
    %
    
    ''
    
     
    
    '
    
      
    
    @Filter
    
     
    
    '
    
     )
    
    '
    
    

    
    
    END
    
    

    
    
    SET
    
     
    
    @Sql
    
     
    
    =
    
     
    
    @Sql
    
       
    
    '
    
    
        SELECT
        top 1 a.*
        FROM
            
    
    '
    
       
    
    @Tables
    
       
    
    '
    
    
        WHERE
            
    
    '
    
       
    
    @Condition
    
      
    
    '
    
    order by CHARINDEX(
    
    '''
    
     
    
    @Keyword
    
     
    
    '''
    
    , product_tag, 1) desc
    
    '
    
     
    
    --
    
    排序根据最匹配的内容放在最前面
    
    

    
    
    
    
    print
    
     
    
    @sql
    
     

    
    
    EXEC
    
     sp_executesql 
    
    @Sql
    
    ,
        N
    
    '
    
    @keyword    nvarchar(500)=NULL
    
    '
    
    ,
        
    
    @Keyword
    
    

    
    END
   
   

   
   

    
    EXEC
    
        
    
    @return_value
    
     
    
    =
    
     
    
    [
    
    dbo
    
    ]
    
    .
    
    [
    
    Search_Test
    
    ]
    
    
        
    
    @keyword
    
     
    
    =
    
     N
    
    '
    
    中秋/春节
    
    '
    
    


    
    SELECT
    
        
    
    '
    
    Return Value
    
    '
    
     
    
    =
    
     
    
    @return_value
   
   

返回的SQL为

   
   

    
    SELECT
    
    
  
    
    top
    
     
    
    1
    
     a.
    
    *
    
    
        
    
    FROM
    
    
            
    
    [
    
    vw_tmp
    
    ]
    
     a 
        
    
    WHERE
    
    
              (a.
    
    [
    
    product_tag
    
    ]
    
      
    
    LIKE
    
     
    
    '
    
    %中秋春节%
    
    '
    
       
    
    or
    
     
    
    [
    
    product_tag
    
    ]
    
     
    
    LIKE
    
     
    
    '
    
    %中秋%
    
    '
    
     
    
    or
    
     
    
    [
    
    product_tag
    
    ]
    
     
    
    LIKE
    
     
    
    '
    
    %春节%
    
    '
    
     )
   
   
   
   

    
     
    
    order
    
     
    
    by
    
     
    
    CHARINDEX
    
    (
    
    '
    
    中秋春节
    
    '
    
    , product_tag, 
    
    1
    
    ) 
    
    desc