CODEWITHSUNDEEP
sql-server
Joel.Cogley
Joel.Cogley

Reputation: 1131

Using variable in SQL LIKE statement

I've got a sproc (MSSQL 2k5) that will take a variable for a LIKE claus like so:

DECLARE @SearchLetter2 char(1)
SET @SearchLetter = 't'
SET @SearchLetter2 = @SearchLetter + '%'
SELECT *
    FROM BrandNames 
    WHERE [Name] LIKE @SearchLetter2 and IsVisible = 1 
    --WHERE [Name] LIKE 't%' and IsVisible = 1 
    ORDER BY [Name]

Unfortunately, the line currently running throws a syntax error, while the commented where clause runs just fine. Can anyone help me get the un-commented line working?

Upvotes: 85

Views: 332296

Answers (12)

Eddie Kumar
Eddie Kumar

Reputation: 1488

Old post but still useful and applicable. This type of issue is primarily caused due to type conversion (especially true if the variable is not of character type (e.g. varchar).

The newer versions of SQL Server (2019 & 2022) do better auto-casting / auto-type-conversion, you are less likely to see this issue in them.

Having said above, to be on the safe side (as others mentioned) use Trim() to avoid any issue that may be caused due to blank spaces.

Trim and Contact Combined:

I have written another example for ease of understanding that use Trim() and Contact() functions:

DECLARE @var VARCHAR(256) = 'your_database_to_find';
SELECT * FROM sys.databases WHERE name LIKE CONCAT('%',TRIM(@var),'%');

OR just:

SELECT * FROM sys.databases WHERE name LIKE '%'+TRIM(@var)+'%'

Note: Trim() was introduced in SQL Server 2017, for older versions use LTRIM() and RTRIM().

Upvotes: 0

Tomasz Wieczorkowski
Tomasz Wieczorkowski

Reputation: 289

But in my opinion one important thing.

The "char(number)" it's the length of the variable.

If we've got a table with "Names" like for example [Test1..Test200] and we declare char(5) in SELECT like:

DECLARE @variable char(5)
SET @variable = 'Test1%'
SELECT * FROM table WHERE Name like @variable

the result will be only - "Test1"! (char(5) - 5 chars in length; Test11 is 6 )

The rest of the potential interested data like [Test11..Test200] will not be returned in the result.

It's ok if we want to limit the SELECT in this way. But if it's not intentional way of doing it could return incorrect results from planned ( Like "all Names begin with Test1..." ).

In my opinion, if we don't know the precise length of a SELECTed value, a better solution could be something like this one:

DECLARE @variable varchar(max)
SET @variable = 'Test1%'
SELECT * FROM <table> WHERE variable1 like @variable

This returns (Test1 but also Test11..Test19 and Test100..Test199).

Upvotes: 9

Gloryson
Gloryson

Reputation: 104

You can also use CONCAT() as follows:

SET @variable='some_value';                                                        
SELECT * FROM table_name WHERE column_name LIKE (SELECT CONCAT('%',@variable,'%'));

Upvotes: 3

Rich L
Rich L

Reputation: 21

I ran into a similar problem. I needed to use just a small piece of a URL saved in my database where the front and ends were irrelevant.

I first attempted to use:

DECLARE @variable VARCHAR(250) = %x%;
SELECT * FROM tblone WHERE column1 LIKE '@variable'

However, this returned the error:

Arithmetic overflow error converting numeric to data type varchar

My working query was formatted:

DECLARE @variable VARCHAR(1000) = x;
SELECT * FROM tblone WHERE column1 LIKE '%'+@variable+'%'

Upvotes: 2

GrzegorzZ
GrzegorzZ

Reputation: 21

I had also problem using local variables in LIKE.
Important is to know: how long is variable.
Below, ORDER_NO is 50 characters long, so You can not use: LIKE @ORDER_NO, because in the end will be spaces.
You need to trim right side of the variable first.
Like this:

DECLARE @ORDER_NO char(50)
SELECT @ORDER_NO = 'OR/201910/0012%'

SELECT * FROM orders WHERE ord_no LIKE RTRIM(@ORDER_NO)

Upvotes: 2

enraged
enraged

Reputation: 401

As Andrew Brower says, but adding a trim

ALTER PROCEDURE <Name>
(
    @PartialName VARCHAR(50) = NULL
)

SELECT Name 
    FROM <table>
    WHERE Name LIKE '%' + LTRIM(RTRIM(@PartialName)) + '%'

Upvotes: 22

Manuel Mendez Warren
Manuel Mendez Warren

Reputation: 11

It may be as simple as LIKE '%%[%3]%%' being [%3] the input variable.

This works for me with SAP B1 9.1

Upvotes: 1

Lively
Lively

Reputation: 31

We can write directly too...

DECLARE @SearchLetter CHAR(1) 

SET @SearchLetter = 'A' 

SELECT * 
FROM   CUSTOMERS 
WHERE  CONTACTNAME LIKE @SearchLetter + '%' 
       AND REGION = 'WY'

or the following way as well if we have to append all the search characters then,

DECLARE @SearchLetter CHAR(1) 

SET @SearchLetter = 'A' + '%' 

SELECT * 
FROM   CUSTOMERS 
WHERE  CONTACTNAME LIKE @SearchLetter 
       AND REGION = 'WY'

Both these will work

Upvotes: 3

Andrew Brower
Andrew Brower

Reputation: 1347

If you are using a Stored Procedure:

ALTER PROCEDURE <Name>
(
    @PartialName VARCHAR(50) = NULL
)

SELECT Name 
    FROM <table>
    WHERE Name LIKE '%' + @PartialName + '%'

Upvotes: 120

JB King
JB King

Reputation: 11910

This works for me on the Northwind sample DB, note that SearchLetter has 2 characters to it and SearchLetter also has to be declared for this to run:

declare @SearchLetter2 char(2)
declare @SearchLetter char(1)
Set @SearchLetter = 'A'
Set @SearchLetter2 = @SearchLetter+'%'
select * from Customers where ContactName like @SearchLetter2 and Region='WY'

Upvotes: 5

FlySwat
FlySwat

Reputation: 175733

DECLARE @SearchLetter2 char(1)

Set this to a longer char.

Upvotes: 4

Eric Sabine
Eric Sabine

Reputation: 1165

Joel is it that @SearchLetter hasn't been declared yet? Also the length of @SearchLetter2 isn't long enough for 't%'. Try a varchar of a longer length.

Upvotes: 24

Related Questions