T-SQL and the WHERE LIKE %Parameter% clause

Question

I was trying to write a statement which uses the WHERE LIKE '%text%' clause, but I am not receiving results when I try to use a parameter for the text. For example, this works:

SELECT Employee WHERE LastName LIKE '%ning%'

This would return users Flenning, Manning, Ningle, etc. But this statement would not:

DECLARE @LastName varchar(max)
SET @LastName = 'ning'
SELECT Employee WHERE LastName LIKE '%@LastName%'

No results found. Any suggestions? Thanks in advance.

Mahmoud Gamal · Accepted Answer · 2018-10-01T21:03:05.723

184

It should be:

...
WHERE LastName LIKE '%' + @LastName + '%';

Instead of:

...
WHERE LastName LIKE '%@LastName%'

edited Oct 01 '18 at 21:03

answered Jan 09 '13 at 14:46

Mahmoud Gamal

75,299
16
132
159

1

thanks for the earlier tip on the question. But it wasn't though. Anyway [in the quest of a high performing answer - is this useful or not?](http://stackoverflow.com/a/14243580/1389394) :) – bonCodigo Jan 09 '13 at 18:37
1

@bonCodigo I don't know really, performance and optimization is not my area. Furthermore, these functions are vendor specific, in your case it depends on how the Oracle RDBMS evaluate them, and I don't know Oracle. Sorry. – Mahmoud Gamal Jan 10 '13 at 07:07
3

This didn't work for me. The % needs to be in the addParameter section. See James Curran answer here http://stackoverflow.com/questions/251276/howto-parameters-and-like-statement-sql – bendecko Sep 12 '14 at 11:56
2

see my answer (currently below). the wildcard-symbol is PART of the SEARCH expression, not part of the sql-query. It is entered by the USER (or, if wildcard-search is predefined, is appended to the users search expression input). If you append it via string concatanation on database-level, you get an unreusable query-string – swe Aug 31 '16 at 09:53

score 18 · Answer 2 · answered Jul 31 '17 at 07:19

18

you may try this one, used CONCAT

WHERE LastName LIKE Concat('%',@LastName,'%')

answered Jul 31 '17 at 07:19

Ramgy Borja

2,064
2
17
37

score 17 · Answer 3 · edited Nov 01 '17 at 09:13

17

The correct answer is, that, because the '%'-sign is part of your search expression, it should be part of your VALUE, so whereever you SET @LastName (be it from a programming language or from TSQL) you should set it to '%' + [userinput] + '%'

or, in your example:

DECLARE @LastName varchar(max)
SET @LastName = 'ning'
SELECT Employee WHERE LastName LIKE '%' + @LastName + '%'

edited Nov 01 '17 at 09:13

Mohammad Shehroz

216
3
11

answered Jun 02 '15 at 14:50

swe

1,389
14
24

T-SQL and the WHERE LIKE %Parameter% clause

3 Answers3

Linked

Related