Display substring in MySQL if the string is less than a specific length or display a custom message if it is more?
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)
Published on 11-Nov-2019 15:19:56
