how to combine firstname and lastname in SQL and search with LIKE

Question

I would like to combine FirstName and LastName into one column called 'FULL NAME' and search with LIKE in SQL.

Example 1:

FirstName : Milan
LastName: Patel

FullName: Milan Patel

Search with: Like '%SomeText%'

Example 2:

Title: " Meetings: A practical alternative to work."

Search: I would like to search the entire word within the give string. ex: work should return the above title.

Here is what I have so far.

SELECT 
    CE.Title, CC.FirstName + ' ' + CC.LastName AS FullName
FROM 
    EntryTable CE
JOIN 
    UserTable CC ON CE.EntryId = CC.USerId
WHERE 
    CC.FirstName LIKE 'n%'

EDIT

I am getting it there slowly. search is working fine with this query.

    SELECT 
     CE.Title 
    FROM 
     EntryTable CE
    JOIN 
     UserTable CC
    ON 
     CE.EntryId = CC.USerId
    WHERE 
     CC.FirstName  + ' ' + CC.LastName LIKE 's%'

BUT it only search for name starting with 's', i would like to search name starting, ending, contains conditions as well. how can i go about that. please help

Answer 1

You can use LIKE with concatenated column values as below

WHERE 
  CC.FirstName + ' ' + CC.LastName LIKE 's%'

BUT it only search for name starting with 's', i would like to search name starting, ending, contains conditions as well. how can i go about that. please help

Then you have to use % before and after search string like below

WHERE 
  CC.FirstName + ' ' + CC.LastName LIKE '%s%'