Display substring in MySQL if the string is less than a specific length or display a custom message if it is more?

MySQLMySQLi Database

For this, you can use substring() function in MySQL. For conditions, use MySQL CASE statement. Let us first create a −

mysql> create table DemoTable1402
   -> (
   -> EmployeeName varchar(40)
   -> );
Query OK, 0 rows affected (0.62 sec)

Insert some records in the table using insert −

mysql> insert into DemoTable1402 values('Adam Smith');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable1402 values('Chris Brown');
Query OK, 1 row affected (0.11 sec)
mysql> insert into DemoTable1402 values('David Miller');
Query OK, 1 row affected (0.16 sec)
mysql> insert into DemoTable1402 values('Carol Taylor');
Query OK, 1 row affected (0.10 sec)

Display all records from the table using select −

mysql> select * from DemoTable1402;

This will produce the following output −

+--------------+
| EmployeeName |
+--------------+
| Adam Smith   |
| Chris Brown  |
| David Miller |
| Carol Taylor |
+--------------+
4 rows in set (0.00 sec)

Here is the query to display substring in MySQL if the string is less than a specific length or display a custom message if it is more −

mysql> select *, case when char_length(EmployeeName) <=11 then substring(EmployeeName,1,5)
   -> else 'EmployeeName is greater than 11'
   -> end as Result
   -> from DemoTable1402;

This will produce the following output −

+--------------+---------------------------------+
| EmployeeName | Result                          |
+--------------+---------------------------------+
| Adam Smith   | Adam                            |
| Chris Brown  | Chris                           |
| David Miller | EmployeeName is greater than 11 |
| Carol Taylor | EmployeeName is greater than 11 |
+--------------+---------------------------------+
4 rows in set (0.04 sec)
raja
Published on 11-Nov-2019 15:19:56
Advertisements