LIKE function in SQL Server 2008 query required

Question

I have a Customer table with two columns Name and Surname (sql Server 2008). The user wants to search by first name or last name - or simply type Jo Bloggs. Suppose the line is full

CustomerId        Name    Surname
1                 Jo      Bloggs

do

select * from customer where Name like '%Jo%'             will find the records
select * from customer where Surname like '%Bloggs%'      will find the records

But how can I make it return records if the user enters a first and last name, such as Jo Bloggs?

Answer 1

This should work whether the user will enter a first name, last name, or both.

SELECT *
FROM customer cus
WHERE ((cus.Name like @FirstName) OR (@FirstName IS NULL)) AND
      ((cus.Surname like @Surname) OR (@Surname IS NULL))

I used two variables because inputting input values ​​into an SQL string is very discouraged since it provides SQL Injection (except slow).

Answer 2

:

ALTER TABLE dbo.customer
   ADD FullName as Name + ' ' + Surname PERSISTED

FullName customer, , :

SELECT (list of fields) FROM dbo.customer 
  WHERE Fullname = 'Jo Bloggs'

, , ,

Answer 3

select * from customer where Name like '%Jo%' and Surname like '%Bloggs%'   

Answer 4

select * from customer where Name like '%Jo%'             
union
select * from customer where Surname like '%Bloggs%'