Split value from one field to two

by

Unfortunately MySQL does not feature a split string function. However you can create a user defined function for this, such as the one described in the following article:

With that function: 

DELIMITER $$

CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255) DETERMINISTIC
BEGIN 
    RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');
END$$

DELIMITER ;

you would be able to build your query as follows:

SELECT SPLIT_STR(membername, ' ', 1) as memberfirst,
       SPLIT_STR(membername, ' ', 2) as memberlast
FROM   users;

If you prefer not to use a user defined function and you do not mind the query to be a bit more verbose, you can also do the following:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(membername, ' ', 1), ' ', -1) as memberfirst,
       SUBSTRING_INDEX(SUBSTRING_INDEX(membername, ' ', 2), ' ', -1) as memberlast
FROM   users;

More Related Contents:

  1. Can MySQL split a column?
  2. How to split the name string in mysql?
  3. Mysql string split [duplicate]
  4. MySQL substring extraction using delimiter
  5. MYSQL – Split Data Into Multiple Rows
  6. ERROR 1698 (28000): Access denied for user ‘root’@’localhost’
  7. MySQL: Grant **all** privileges on database
  8. Multiple Updates in MySQL
  9. Does MySQL ignore null values on unique constraints?
  10. MySQL Foreign Key Error 1005 errno 150 primary key as foreign key
  11. What does the KEY keyword mean?
  12. MySQL – Subtracting value from previous row, group by
  13. How to store a datetime in MySQL with timezone info
  14. MySQL utf8mb4, Errors when saving Emojis
  15. How to store unicode in MySQL?
  16. mysql after insert trigger which updates another table’s column
  17. Error Code: 1406. Data too long for column – MySQL
  18. Search for string within text column in MySQL
  19. Mysql database sync between two databases
  20. Clear MySQL query cache without restarting server
  21. Mysql select recursive get all child with multiple level
  22. Does mysql have the equivalent of Oracle’s “analytic functions”?
  23. MySQL Error: Incorrect usage of UPDATE and LIMIT
  24. Mysql insert into 2 tables
  25. The web application [] appears to have started a thread named [Abandoned connection cleanup thread] com.mysql.jdbc.AbandonedConnectionCleanupThread
  26. Execute INSERT if table is empty?
  27. MySQL: Querying for unicode entities
  28. undefined method `eq’ for nil:NilClass with rails 3 and ruby enterprise on ubuntu hardy
  29. MySQL data directory location
  30. INSERT INTO fails with node-mysql

Leave a Comment