How to split the name string in mysql?

by

I’ve separated this answer into two(2) methods. The first method will separate your fullname field into first, middle, and last names. The middle name will show as NULL if there is no middle name.

SELECT
   SUBSTRING_INDEX(SUBSTRING_INDEX(fullname, ' ', 1), ' ', -1) AS first_name,
   If(  length(fullname) - length(replace(fullname, ' ', ''))>1,  
       SUBSTRING_INDEX(SUBSTRING_INDEX(fullname, ' ', 2), ' ', -1) ,NULL) 
           as middle_name,
   SUBSTRING_INDEX(SUBSTRING_INDEX(fullname, ' ', 3), ' ', -1) AS last_name
FROM registeredusers

This second method considers the middle name as part of the lastname. We will only select a firstname and lastname column from your fullname field.

SELECT
   SUBSTRING_INDEX(SUBSTRING_INDEX(fullname, ' ', 1), ' ', -1) AS first_name,
    TRIM( SUBSTR(fullname, LOCATE(' ', fullname)) ) AS last_name
FROM registeredusers

There’s a bunch of cool things you can do with substr, locate, substring_index, etc. Check the manual for some real confusion. http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

More Related Contents:

  1. how to delete data from table A besides the customer service and director data in table B?
  2. SQL split values to multiple rows
  3. Get records with max value for each group of grouped SQL results
  4. How to populate a table with a range of dates?
  5. MYSQL OR vs IN performance
  6. SELECTING with multiple WHERE conditions on same column
  7. ERROR 1452: Cannot add or update a child row: a foreign key constraint fails
  8. Getting “Lock wait timeout exceeded; try restarting transaction” even though I’m not using a transaction
  9. Cannot delete or update a parent row: a foreign key constraint fails
  10. Using union and order by clause in mysql
  11. Using an Alias in SQL Calculations
  12. How do I select an entire row which has the largest ID in the table?
  13. MySQL/MariaDB – order by inside subquery
  14. Can MySQL replace multiple characters?
  15. OUTPUT Clause in MySQL
  16. Using ORDER BY and GROUP BY together
  17. Sorting by date & time in descending order?
  18. Case statement in MySQL
  19. MySQL Pivot Table Column Data as Rows
  20. updating columns with a sequence number mysql
  21. MySQL, Concatenate two columns
  22. Select a column with a keyword name
  23. ERROR 1148: The used command is not allowed with this MySQL version
  24. Use Composite Primary Key as Foreign Key
  25. How to select data where a field has a min value in MySQL?
  26. How to SELECT by MAX(date)?
  27. get a recursive parent list
  28. MYSQL – Split Data Into Multiple Rows
  29. How do you force mysql LIKE to be case sensitive? [duplicate]
  30. In what order are MySQL JOINs evaluated?

Leave a Comment