How to concatenate row values for use in WHERE clause of T-SQL query

Question

I want to write a query in T-SQL to perform a search on two concatenated columns. The two columns are fname and lname. Here is what I have so far:

SELECT
    fname,
    lname,
    ...
FROM
    users
JOIN
    othertable ON foo=bar
WHERE
    fname+' '+lname LIKE '%query%'

SQL server doesn't like that syntax, though. How do I structure the query so that I can perform a WHERE LIKE operation that searches through two concatenated columns, allowing me to search the user's full name, rather than just first name and last name individually?

Answer 1

I can only suggest that one of fname or lname is NULL so the LIKE fails., (NULL concat anything is null)

Try

...
ISNULL(fname, '') + ' ' + ISNULL(lname, '') LIKE '%query%'

However, I would use a computed column and consider indexing it because this will run awfully.

Answer 2

My suggestion is to add a calculated column to your table for full_name calculated column examples:

--drop table #test
create table #test (test varchar (10) , test2 varchar (5),[Calc]  AS right(test, 3))
Insert #test
values('hello', 'Bye')
Insert #test
values('hello-bye', null)


Alter table #test
add [MyComputedColumn]  AS substring(test,charindex('-',test), len(test)),
Concatenatedcolum as test+ ' ' +test2
select * from #test

As you can see you may have to play around a bit until you get the results you want. Do that in a temp table first to avoid having to restructure the database table multiple times. For names, especially if you are using middle name which is often blank, you may need to add some code to handle nulls. You may also need to have code sometimes to cast to the same datatype if one filed you are concatenating is an int for instance and the other a varchar.