How to create new table with first name only in table

I have some data that looks like this:

+--------------------+--------------------+--------------------+--------------------+-----------+--------------------+-------+-------------+-------------+--------------------+-----------+------------+-----+-----+
|             address|          attributes|         business_id|          categories|       city|               hours|is_open|     latitude|    longitude|                name|postal_code|review_count|stars|state|
+--------------------+--------------------+--------------------+--------------------+-----------+--------------------+-------+-------------+-------------+--------------------+-----------+------------+-----+-----+
|2818 E Camino Ace...|[,,,,,,,,,,,,,,,,...|1SWheh84yJXfytovI...|   Golf, Active Life|    Phoenix|                null|      0|   33.5221425| -112.0184807|Arizona Biltmore ...|      85016|           5|  3.0|   AZ|
|30 Eglinton Avenue W|[,, u'full_bar', ...|QXAEGFB4oINsVuTFx...|Specialty Food, R...|Mississauga|[9:0-1:0, 9:0-0:0...|      1|43.6054989743|-79.652288909|Emerald Chinese R...|    L5R 3E7|         128|  2.5|   ON|
+--------------------+--------------------+--------------------+--------------------+-----------+--------------------+-------+-------------+-------------+--------------------+-----------+------------+-----+-----+

I’d like to create a new table with the name column but with the first name only.

Answer

This gets the first substring before the space character in name as first_name.

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(name, ' ', 1), ' ', -1) AS first_name
FROM MyTable

first_name
Arizona
Emerald

Advertisement

Answer