How to combine first and last name in SQL and do a search using LIKE

Question

I would like to combine FirstName and LastName into one column named 'FULL NAME' and do a search using LIKE in SQL.

Example 1:

Name: Milan
LastName: Patel

FullName: Milan Patel

Search with: Like '%SomeText%'

Example 2:

Title: "Meetings: A Practical Alternative to Work."

Search: I would like to find the whole word in the input line. ex: work should return the above header.

Here is what I still have.

 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 get it there slowly. search works 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 searches for a name starting with 's' , I would like to search for a name, starting, ending, also contains conditions. how can i get around this. please, help

Answer 1

You can use LIKE with concatenated column values ​​below

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

BUT only search for a name starting with 's', I would like to search for a name that starts, ends, also contains conditions. how can i go that. please, help

Then you should use % before and after the search bar as shown below

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

Answer 2

you can use concat

  SELECT CE.Title, concat(CC.FirstName , ' ' , CC.LastName) AS FullName FROM EntryTable CE JOIN UserTable CC ON CE.EntryId = CC.USerId WHERE concat(CC.FirstName , ' ' , CC.LastName) LIKE 'n%' OR CC.FirstName LIKE 'n%' OR CC.FirstName LIKE '%n' OR CC.LastName LIKE 'n%' OR CC.LastName LIKE '%n' ^^--// (1). ^^--// (2) 

(1) to find the last name in the first search line.

(2) to find the last name at the end of the line you are looking for.

(3) to find the last name in the middle of the line, do this "% n%"

Answer 3

you cannot use ALIAS in the WHERE , because in the SQL order of the operation, the WHERE is executed before the SELECT .

better use full concatenation - this is the WHERE

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

if you don’t want to use it, wrap your request in a subquery to use ALIAS ,

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

Answer 4

 SELECT some_column_name FROM some_table where firstname + ' ' + lastname LIKE 'some_value' 

no FullName .

Answer 5

Where CONCAT (CC.FirstName, '', CC.LastName) Like "% n%"

Answer 6

Combine them also in the WHERE clause with two% signs.