Split the left part of a string by a separator string in MySQL?

MySQLMySQLi Database

You can use substring_index() function from MySQL to split the left part of a string. The syntax is as follows −

SELECT
yourColumnName1,.....N,SUBSTRING_INDEX(yourColumnName,’yourSeperatorSymbol’,1)
as anyVariableName from yourTableName;

The value 1 indicates that you can get left part of string. To check the above syntax, let us create a table. The query to create a table is as follows −

mysql> create table LeftStringDemo
   -> (
   -> Id int,
   -> Words varchar(100)
   -> );
Query OK, 0 rows affected (0.92 sec)

Insert some records in the table using insert command. The query is as follows −

mysql> insert into LeftStringDemo values(1,'MySQL==6789');
Query OK, 1 row affected (0.19 sec)

mysql> insert into LeftStringDemo values(2,'Java==Object Oriented');
Query OK, 1 row affected (0.21 sec)

mysql> insert into LeftStringDemo values(3,'C Language==Procedural Programming');
Query OK, 1 row affected (0.18 sec)

mysql> insert into LeftStringDemo values(4,'PL/SQL==Structured Programming');
Query OK, 1 row affected (0.16 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from LeftStringDemo;

The following is the output

+------+------------------------------------+
| Id   | Words                              |
+------+------------------------------------+
|    1 | MySQL==6789                        |
|    2 | Java==Object Oriented              |
|    3 | C Language==Procedural Programming |
|    4 | PL/SQL==Structured Programming     |
+------+------------------------------------+
4 rows in set (0.00 sec)

Here is the query that gets the left part of the string column ‘Words’ on the basis of a separator string ‘==’

mysql> select Id, substring_index(Words, '==', 1) as OnlyLefthandsideValue from
LeftStringDemo;

The following is the output

+------+-----------------------+
| Id   | OnlyLefthandsideValue |
+------+-----------------------+
|    1 | MySQL                 |
|    2 | Java                  |
|    3 | C Language            |
|    4 | PL/SQL                |
+------+-----------------------+
4 rows in set (0.00 sec)
raja
Published on 16-Jan-2019 16:34:28
Advertisements