Get all characters before space in MySQL?

MySQLMySQLi Database

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.

raja
Published on 11-Jan-2019 08:08:58
Advertisements