MySQL query to select everything to left of last space in a column with name records

For this, you can use LEFT(). Let us first create a table −

mysql> create table DemoTable1939    (    FullName varchar(20)    ); Query OK, 0 rows affected (0.00 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable1939 values('Adam Smith'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1939 values('Robert Downey, Jr.'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1939 values('Sylvester Stallone'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1939 values('Chris Hemsworth'); Query OK, 1 row affected (0.00 sec)

Display all records from the table using select statement −

mysql> select * from DemoTable1939;

This will produce the following output −

+--------------------+ | FullName           | +--------------------+ | Adam Smith         | | Robert Downey, Jr. | | Sylvester Stallone | | Chris Hemsworth    | +--------------------+ 4 rows in set (0.00 sec)

Here is the query to select everything to left of last space

mysql> select    LEFT(FullName, LENGTH(FullName) - LOCATE(' ', REVERSE(FullName))+1)    from DemoTable1939;

This will produce the following output −

+---------------------------------------------------------------------+ | LEFT(FullName, LENGTH(FullName) - LOCATE(' ', REVERSE(FullName))+1) | +---------------------------------------------------------------------+ | Adam                                                                | | Robert Downey,                                                      | | Sylvester                                                           | | Chris                                                               | +---------------------------------------------------------------------+ 4 rows in set (0.00 sec)