T-SQL and the WHERE LIKE %Parameter% clause

SqlSql Server-2008Tsql

Sql Problem Overview

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.

Sql Solutions

Solution 1 - Sql

It should be:

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

Instead of:

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

Solution 2 - Sql

you may try this one, used CONCAT

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

Solution 3 - Sql

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 + '%'
Categories
Recommended Sql Solutions
Recommended Sql Server-2008 Solutions
Recommended Tsql Solutions

Previous Solution:

What character represents a new line in a text area

HtmlTextareaNewline

Next Solution:

What can cause a “Resource temporarily unavailable” on sock send() command

CLinuxSocketsUnix

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
Questiondp3View Question on Stackoverflow
Solution 1 - SqlMahmoud GamalView Answer on Stackoverflow
Solution 2 - SqlRamgy BorjaView Answer on Stackoverflow
Solution 3 - SqlsweView Answer on Stackoverflow