PHP SQL Server Concat

Trying to use SQL Server with a jQuery autocomplete. I can get the开发者_如何学Python following to work when just checking the term entered with a matching domain, but I would also like the autocomplete to check if a match is found for the contact name (first name and last name).

Is there a way (like in mySQL) to concat the fname and lname?

Domain only:

($term = data entered in autocomplete box)

SELECT distinct comp_companyid, comp_name, comp_emailaddress, comp_website, pers_firstname, pers_lastname, addr_address1, addr_address2, addr_city, addr_state, addr_postcode
FROM company, person, address, address_link
WHERE pers_companyid = comp_companyid
  AND addr_addressid = adli_addressid
  AND adli_companyid = comp_companyid
  AND comp_website LIKE '%".$term."%';

My attempt at matching name as well:

SELECT distinct comp_companyid, comp_name, comp_emailaddress, comp_website, pers_firstname, pers_lastname, addr_address1, addr_address2, addr_city, addr_state, addr_postcode
FROM company, person, address, address_link
WHERE pers_companyid = comp_companyid
  AND addr_addressid = adli_addressid
  AND adli_companyid = comp_companyid
  AND comp_website LIKE '%".$term."%'
  OR pers_firstname + ' ' + pers_lastname LIKE '%".$term."%';

In addition to security concerns, this is going to be slow. You'll need to create a virtual view with a column equal to the first + last. (In Ms SQL, I believe this would look like):

select a.id, v.fullname 
 from (select concat(first_name, " ", last_name) as fullname, id) v 
where a.id = v.id 
and fullname like '%searchterm%'

Since you haven't told Ms SQL to index on this combination yet, it's going to take a while. You should look into more of a fulltext search solution.

You could match first OR last with the term like:

WHERE ( 
     firstname like '%searchterm%' 
  or lastname like '%searchterm%' 
  or comp_website like '%searchterm%') 
AND [other_clauses_here]

This will be faster. Eventually, this approach runs out of steam, though, which is when you want to consider a better fulltext search solution.

Figured it out:

SELECT * FROM <TABLES> WHERE (website LIKE '%".$term."%' OR (firstname +' '+ lastname) LIKE '%".$term."%';