CODEWITHSUNDEEP
mysqldatabase
Bobby
Bobby

Reputation: 113

How can I extract a last name from full name in mysql?

I have a table with fullname column. I want to make a query for finding a person via his last name but his last name is in the full name column.

Upvotes: 1

Views: 7574

Answers (4)

rowmoin
rowmoin

Reputation: 708

You can use this if you want to fetch by query:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX( `fullname` , ' ', 2 ),' ',1) AS b, 
SUBSTRING_INDEX(SUBSTRING_INDEX( `fullname` , ' ', -1 ),' ',2) AS c FROM `users` WHERE `userid`='1'

But you can also try by PHP to fetch last name. You just use explode function to fetch last name.

Exm:

$full_name = "row moin";
$pieces = explode(" ", $fullname);
echo $first_name = $pieces[0]; // row
echo $last_name =  $pieces[1]; // moin

Upvotes: 1

Talvid
Talvid

Reputation: 1

SELECT t.ContactName, 
  SUBSTRING(
    t.ContactName, 
    CHARINDEX(' ', t.ContactName) + 1, 
    (DATALENGTH(t.ContactName) - CHARINDEX(' ', t.ContactName)) + 1
  ) AS last_name
    FROM dbo.t_w3_schools_customers AS t
    ORDER BY 1;

Upvotes: 0

Haseeb Javed
Haseeb Javed

Reputation: 93

A simple answer for this is like this suppose we have a name

Charles Dickens

:

SELECT * FROM TABLE_NAME WHERE SUBSTRING_INDEX(FULLNAME,' ',-1) like '%Dickens';

Upvotes: 0

DS501
DS501

Reputation: 133

Would it matter if it accidentally returned someone whose first name matched your query?

A simple query would be:

SELECT *
FROM TABLE
WHERE fullname LIKE '%insertlastname%'

If you want to define the last name as the name after the last space:

SELECT substring_index(fullname, ' ', -1) as lastname
FROM TABLE
WHERE lastname='insertlastname'

Two suboptimal answers, but some answers at least.

Upvotes: 1

Related Questions