TSQL: Exact match with a SP using a LIKE?

Question

Hello, I've a SP using a LIKE. Let's simplify it to: SELECT * FROM customers WHERE name LIKE '%' + @query + '%'. I'd like to use it for an (optional) exact match search without altering the SP but with a tricky parameter ;) Is there a way to "cancel" the 2 '%' with a clever @query? Thanks.

Answer 1

Why does it have to be this way? Something like this makes more sense (untested, probably has some bugs/typos)

create procedure match
    @needle varchar(max), @mode int
as
begin
    declare @query varchar(max)
    if @mode = 1 begin
        set @query = 'SELECT * FROM customers WHERE name LIKE ''%' + @needle + '%'''
    end
    else begin
        set @query = 'SELECT * FROM customers WHERE name = ''' + @needle + '''
    end
    exec @query
end

EDIT: Since you wish to avoid altering the prototype, you can simulate the mode parameter, or simply send the string with the %s

create procedure match
    @modeneedle varchar(max)
as
begin
    declare @mode varchar(1)
    declare @needle varchar(max)

    set @mode=substring(@modeneedle,1,1)
    set @needle=substring(@modeneedle,2,len(@modeneedle))

    declare @query varchar(max)
    if @mode = '1' begin
        set @query = 'SELECT * FROM customers WHERE name LIKE ''%' + @needle + '%'''
    end
    else begin
        set @query = 'SELECT * FROM customers WHERE name = ''' + @needle + '''
    end
    exec @query
end

Answer 2

Sure, use a variable to hold the two % signs, and set it to an empty string when you want an exact match.

Using the basic answer from Vinko, you could have

create procedure match
    @needle varchar(max), @mode int
as
begin
declare @like char(1);

set @like = case when @like = 1 then '%' else '' end

SELECT * FROM customers WHERE name LIKE (@like + @needle + @like)
end

Answer 3

Note: I would never, ever recommend doing this. It's a sign that you're doing it really, really wrong. Having said that, if you really want to do your own SQL injection, and your stored procedure's like this:

set @query = 'SELECT * FROM tmpCustomer WHERE name LIKE ''%' + @needle + '%'''
exec (@query)

Then running your stored procedure like this:

exec match '~~~~THIS WILL NEVER BE IN THE DATABASE~~~~'' OR name = ''testing'' OR ''notapercentagesign'' = '''

...should do an exact match search for 'testing'.

But frankly, if you need to do stuff like this with your own computer system, then you've lost, and here's some evidence: running your stored procedure like this:

exec match '''; DELETE FROM Customer; SELECT * FROM Customer WHERE name LIKE '' '

...will delete all your Customer records :)

Answer 4

try:

select *
from customers
where case @exact then name = @query else name like '%' + @query + '%' end;