How to concatenate string values ​​for use in a WHERE clause of a T-SQL query

Question

I want to write a query in T-SQL to do a search on two concatenated columns. Two columns: fname and lname. Here is what I still have:

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

The SQL server does not like this syntax. How to structure the query so that I can perform a WHERE LIKE operation that scans two concatenated columns, which allows me to search for the full name of the user, and not just the first and last name separately?

Answer 1

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

Try

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

However, I would use a computed column and think about indexing it, because it will work terribly.

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 a bit until you get the results you need. First, do this in the temporary table to avoid having to restructure the database table several times. For names, especially if you use a middle name that is often empty, you may need to add code to handle zeros. You may also need to sometimes use the code to include in the same data type if one of you filed for concatenation, for example, int, and the other - varchar.

Answer 3

I think one of the connection conditions may cause a problem. Try rewriting it, you may find that the error goes away;)