Cacher is the code snippet organizer for pro developers

Example 1 - user name

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

Example 2 - email

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(email, '@', 1), '@', -1) AS email_name,
       SUBSTRING_INDEX(SUBSTRING_INDEX(email, '@', 2), '@', -1) AS email_domain
FROM   users

Example 3 - splitting a full name into first, middle, and last

Here is the select:

SELECT u.`full_name`
, LENGTH(u.`full_name`)-LENGTH(REPLACE(u.`full_name`,' ',''))+1 AS word_count
-- first name
,SUBSTRING_INDEX(SUBSTRING_INDEX(u.`full_name`, ' ', 1), ' ', -1) AS first_name
-- middles names
-- 3. trim leading & trailing spaces from #2
-- 2. trim last name from #1
-- 1. trim first name from u.full_name
,TRIM(BOTH ' ' FROM TRIM(TRAILING SUBSTRING_INDEX(SUBSTRING_INDEX(u.`full_name`, ' ', LENGTH(u.`full_name`)-LENGTH(REPLACE(u.`full_name`,' ',''))+1), ' ', -1) FROM TRIM(LEADING SUBSTRING_INDEX(SUBSTRING_INDEX(u.`full_name`, ' ', 1), ' ', -1) FROM u.full_name))) AS middle_name
-- last name
,SUBSTRING_INDEX(SUBSTRING_INDEX(u.`full_name`, ' ', LENGTH(u.`full_name`)-LENGTH(REPLACE(u.`full_name`,' ',''))+1), ' ', -1) AS last_name
       
FROM   database_name.`User` AS u;

Output:

full_name   = John George Smith
word_count  = 3
first_name  = John
middle_name = George
last_name   = Smith

Here is the update:

UPDATE database_name.`User` AS u
SET u.`forename` = SUBSTRING_INDEX(SUBSTRING_INDEX(u.`full_name`, ' ', 1), ' ', -1)
,u.`middle` = TRIM(BOTH ' ' FROM TRIM(TRAILING SUBSTRING_INDEX(SUBSTRING_INDEX(u.`full_name`, ' ', LENGTH(u.`full_name`)-LENGTH(REPLACE(u.`full_name`,' ',''))+1), ' ', -1) FROM TRIM(LEADING SUBSTRING_INDEX(SUBSTRING_INDEX(u.`full_name`, ' ', 1), ' ', -1) FROM u.full_name)))
,u.`surname` = SUBSTRING_INDEX(SUBSTRING_INDEX(u.`full_name`, ' ', LENGTH(u.`full_name`)-LENGTH(REPLACE(u.`full_name`,' ',''))+1), ' ', -1)

Example 4 - with select

SELECT 
SUBSTRING_INDEX(SUBSTRING_INDEX(c.`Phone1`, ' (', 1), ' ', -1) AS ph1
,SUBSTRING_INDEX(SUBSTRING_INDEX(c.`Phone1`, ' (', 2), ' ', -1) AS ext
,u.Phone1
,u.*
FROM database_name.`User` u
WHERE u.`Phone1` LIKE '%)'
AND u.`Phone1` NOT RLIKE 'ext'
HAVING ext LIKE '(%'
AND ext REGEXP '^-?[()0-9]+$'

Example 5 - with update

UPDATE database_name.`User` u
SET u.`phone1_ext` = SUBSTRING_INDEX(SUBSTRING_INDEX(c.`Phone1`, ' (', 2), ' ', -1)
WHERE u.`Phone1` LIKE '%)'
AND u.`Phone1` NOT RLIKE 'ext'
AND SUBSTRING_INDEX(SUBSTRING_INDEX(c.`Phone1`, ' (', 2), ' ', -1) LIKE '(%'
AND SUBSTRING_INDEX(SUBSTRING_INDEX(c.`Phone1`, ' (', 2), ' ', -1) REGEXP '^-?[()0-9]+$'
;