patel.milanb patel.milanb - 4 months ago 8
SQL Question

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

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

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%'
Comments