How to use LIKE and CONCAT() with variable in T-SQL

Question

I am working on a query using T-SQL.

Goal: return rows that contain a name stored in a variable.

My issue: the query returns all rows, without filtering.

Additional info: The names are stored as varchar and there are several spaces after the letters which is why I want to concatenate the variable with '%'.

Also, when I ran the query with just one '%' appended on the end of the variable, the database returned all records beginning with 'D'.

My query:

DECLARE @fName as varchar;
SET @fName='DILLON';

SELECT DISTINCT 
    e.FIRST_NAME, e.LAST_NAME, e.EMAIL_ADDRESS
FROM 
    [172.20.11.11].LSLMDB.dbo.vwEmployee e
JOIN 
    [172.20.11.11].LSLMDB.ls_apps.PAEMPLOYEE pa ON pa.EMPLOYEE = e.EMPLOYEE
JOIN
    [172.20.11.11].LSLMDB.ls_apps.PCODES pc ON pa.LOCAT_CODE = pc.CODE
JOIN
    [172.20.11.11].LSLMDB.ls_apps.PCODESDTL pcd ON pc.CODE = pcd.CODE 
WHERE 
    e.FIRST_NAME LIKE CONCAT('%',@fName,'%');

I appreciate any attempts to help!

Always, always declare your varchar variables with an explicit length. Lest you end up with one that's unexpectedly only one character long, like you're running into now. It's a bad habit to kick. — Jeroen Mostert
– Jeroen Mostert, Commented Aug 3, 2017 at 16:45
While the solution was fairly simple to solve, this question was very well asked... thanks for putting in the effort and welcome to SO Dillion. — Hogan
– Hogan, Commented Aug 3, 2017 at 16:53

Hogan · Accepted Answer · 2017-08-03 16:51:17Z

8

The problem is the system is defaulting to DECLARE @fName as varchar(1) -- this is probably not what you want. Change to DECLARE @fName as varchar(100); This should work as expected.

the query returns all rows, without filtering.

No it is returning all rows that contain D. Depending on coallation settings this might include both upper and lower case matches

Also, when I ran the query with just one '%' appended on the end of the variable, the database returned all records beginning with 'D'.

Since it was just a single character the filter became D% which would return all records starting with D.

edited Aug 3, 2017 at 16:51

answered Aug 3, 2017 at 16:47

Hogan

70.7k10 gold badges83 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

anondev Over a year ago

Sweet, thanks! Overlooked that... My rep won't let the vote change publicly, just so you know.

Hogan Over a year ago

@DillonHarless -- I think you just have to wait for a few minutes before you can vote at your level. Good Luck.

Collectives™ on Stack Overflow

How to use LIKE and CONCAT() with variable in T-SQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related