Question

in my optional parameter query that uses LIKE operator , Escape special characters is not working . this is part of the query

NAME LIKE  CASE WHEN  % +@Name+ %  IS NULL THEN NAME ELSE  % +@Name+ %  END

i) 认为,

NAME LIKE CASE WHEN  % +@Name+ % ESCAPE    IS NULL THEN NAME ELSE  % +@Name+ %  END

but when i add escape but it raise an error . any help ??

《刑法》:

 set @Name= Restaur[ant 

      SELECT      [JOB].HOUSE_CODE,  [JOB].JOB_CODE,  [JOB].OPEN_DATE,  [JOB].NAME AS Position,  [JOB].DESCRIPTION,  [RESTAURANT].ZIP_CODE AS ZipCode, 
                           [STATE].STATE_NAME AS State,  [RESTAURANT].CITY_NAME AS City
    FROM          [JOB] INNER JOIN
                           [RESTAURANT] ON  [JOB].HOUSE_CODE =  [RESTAURANT].HOUSE_CODE INNER JOIN
                           [STATE] ON  [RESTAURANT].STATE_CODE =  [STATE].STATE_CODE INNER JOIN
                           [JOB_CODE] ON  [JOB].HOUSE_CODE =  [JOB_CODE].HOUSE_CODE AND  [JOB].JOB_CODE =  [JOB_CODE].JOB_CODE AND 
                           [RESTAURANT].HOUSE_CODE =  [JOB_CODE].HOUSE_CODE
    WHERE     (  [JOB].NAME LIKE  CASE WHEN  % +@Name+ %  IS NULL THEN  [JOB].NAME ELSE  % +@Name+ %  END ESCAPE    )

Answer 1

在化学文摘社国际协会发言之外指定亚太经社会。而且,它足以为全国人民力量测试@Name。

NAME LIKE CASE WHEN @Name IS NULL THEN NAME ELSE  % +@Name+ %  END ESCAPE ``

www.un.org/Depts/DGACM/index_spanish.htm 这里有一些示范法,以说明这一方法。

declare @test table (
    name varchar(100)
)

insert into @test
    (name)
    select  I am 100% confident this works.  union all
    select  There can be no doubt.  union all
    select  I would like to eat in fine restaur[ant some day. 

declare @name varchar(100)

/* Case when @name is NULL */
select name
    from @test
    where name like case when @name is null then name else  % +@name+ %  end escape   

set @name =  100\% 

/* Case when @name has a value with a % */
select name
    from @test
    where name like case when @name is null then name else  % +@name+ %  end escape   

set @name =  restaur[ant 

/* Case when @name has a value with a [ */
select name
    from @test
    where name like case when @name is null then name else  % +@name+ %  end escape

Answer 2

Have you tried putting a space before ESCAPE?

+ % ESCAPE    IS

为此:

+ %  ESCAPE    IS

Answer 3

你们期待它做些什么? 我尝试了你的第一个例子,它给我期望它:

CREATE TABLE test2 (
NAME varchar(100)
)

GO

INSERT test2(name) values( Fred )
INSERT test2(name) values( bert )

GO
DECLARE @name VARCHAR(100)
--SET @name= re 
SELECT * FROM test2
WHERE NAME LIKE CASE WHEN  % +@Name+ %  IS NULL THEN NAME ELSE  % +@Name+ %  END

如果我拿到“Name”的数值,那么如果我离开该名称,它就只能填上该数值的返回姓名?

Answer 4

Do you really want to compare % +@Name+ %ESCAPE to null? I think you want to put the escape keyword later on, like so:

CASE WHEN  % +@Name+ %  IS NULL THEN NAME ELSE  % +@Name+ %  ESCAPE    END

友情链接