Get all characters before space in MySQL?


In order to get all characters before space in MySQL, you can use left() function from the MySQL. The syntax is as follows −

select left(yourColumnName,LOCATE(' ',yourColumnName) - 1) as anyVariableName from yourTableName;

To understand the above concept, let us create a table.

The query to create a table is as follows −

mysql> create table AllCharacterBeforeSpace
   −> (
   −> FirstNameAndLastName varchar(200)
   −> );
Query OK, 0 rows affected (0.51 sec)

Now you can insert some records in the table.

The query to insert records is as follows −

mysql> insert into AllCharacterBeforeSpace values('John Smith');
Query OK, 1 row affected (0.15 sec)

mysql> insert into AllCharacterBeforeSpace values('Carol Taylor');
Query OK, 1 row affected (0.13 sec)

mysql> insert into AllCharacterBeforeSpace values('Maria Garcia');
Query OK, 1 row affected (0.16 sec)

mysql> insert into AllCharacterBeforeSpace values('James Johnson');
Query OK, 1 row affected (0.17 sec)

Now you can display all records from the table with the help of select statement.

The query is as follows −

mysql> select *from AllCharacterBeforeSpace;

The following is the output −

+----------------------+
| FirstNameAndLastName |
+----------------------+
| John Smith           |
| Carol Taylor         |
| Maria Garcia         |
| James Johnson        |
+----------------------+
4 rows in set (0.00 sec)

Here is the query that can be used to get all characters before space from the table.

The query is as follows −

mysql> select left(FirstNameAndLastName,LOCATE(' ',FirstNameAndLastName) - 1) as FirstNameBeforeSpace
   −> from AllCharacterBeforeSpace;

The following is the output −

+----------------------+
| FirstNameBeforeSpace |
+----------------------+
| John                 |
| Carol                |
| Maria                |
| James                |
+----------------------+
4 rows in set (0.03 sec)

The above query returns an empty string when it does not get any space in the column.

Updated on: 30-Jul-2019

645 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements