How to substring value in a MySQL table column?

MySQLMySQLi Database

To substring a MySQL table column, use the in-built SUBSTR() function from MySQL. The syntax is as follows −

select substr(yourColumnName,AnyValue) as anyVariableName from yourTableName;

To understand the function substr(), let us create a table. The query to create a table is as follows −

mysql> create table SubStringDemo
   −> (
   −> UserId varchar(200)
   −> );
Query OK, 0 rows affected (0.55 sec)

Now insert some records in the table. The query to insert records is as follows −

mysql> insert into SubStringDemo values('Bob10015');
Query OK, 1 row affected (0.29 sec)

mysql> insert into SubStringDemo values('Smith0015');
Query OK, 1 row affected (0.22 sec)

mysql> insert into SubStringDemo values('Carol20010');
Query OK, 1 row affected (0.14 sec)

Now you can display all records from the table with the help of select statement. The query is as follows −

mysql> select *from SubStringDemo;

The following is the output −

+------------+
| UserId     |
+------------+
| Bob10015   |
| Smith0015  |
| Carol20010 |
+------------+
3 rows in set (0.00 sec)

The following is the query to substring MySQL table column −

mysql> select substr(UserId,5) as ExtractSubstring from SubStringDemo;

Here is the output that displays the substrings −

+------------------+
| ExtractSubstring |
+------------------+
| 0015             |
| h0015            |
| l20010           |
+------------------+
3 rows in set (0.00 sec)
raja
Published on 11-Jan-2019 10:26:26
Advertisements