T-SQL Search on 'combined' column name

I would like to know if its possible to search on two combined columns. For instance in my application I have an input field for 'Full Name', but in the开发者_Go百科 SQL Database I have columns Name, Surname.

Is it possible to construct a query to do a search like Name + Surname = %Full Name% ?

You can do this:

select  *
from Production.Product
where (Name + ' ' + ProductNumber) like 'Bearing Ball BA-8327'

However, if you want to take advantage of indexing, you better split you your input parameter first and then use direct field comparison.

Yes you can do this, but it will be quite slow. Since Name and Surname can be indexed.

Expanding on the suggestions from previous answers, try this.

SELECT * FROM People
WHERE firstname = substring(@fullname, 1, charindex(' ', @fullname) - 1)
AND surname = substring(@fullname, charindex(' ', @fullname) + 1, len(@fullname))

Hope this helps.

If disk space is not a concern you can add a 'persistent' calculated column with the fullname this way you can maintain the specifics of two specific columns while achieving the possibility of indexing the full name column that is auto updated when either first or last name changes.

The caveats are the extra space needed as well as the somewhat slower inserts. If these two situations are a concern you can use the calculated columns without persistence to get a transparent fullname column and cleaner queries.

http://msdn.microsoft.com/en-us/library/ms189292.aspx