How to use LIKE and CONCAT () with a variable in T-SQL
I am working on a query using T-SQL.
Purpose : Return strings containing the name stored in a variable.
My problem : the query returns all rows without filtering.
Additional information . The names are stored as varchar
, and there are some spaces after the letters, so I want to concatenate the variable with "%".
Also, when I ran the query with only one "%" appended to the end of the variable, the database returned all records starting with "D".
My request :
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!
source to share
The problem is, the system defaults to a value 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 returns all rows containing D
. Depending on the coalition settings, this can include both upper and lower case matching
Also, when I ran the query with only one "%" appended to the end of the variable, the database returned all records starting with "D".
Since it was only one character, the filter became D%
, which will return all records starting with D.
source to share