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)

AmitDiwan

Updated on 11-Nov-2019 10:50:57

• Related Questions & Answers
• MySQL IF() to display custom YES or NO messages
• How to display the result more readable if the string is long stored in MySQL VARCHAR?
• Find if there is a path of more than k length from a source in C++
• Find if there is a path of more than k length from a source in Python
• Check if the frequency of any character is more than half the length of the string in Python
• Check if any value in an R vector is greater than or less than a certain value.
• Check if table exists in MySQL and display the warning if it exists?
• Count values greater and less than a specific number and display count in separate MySQL columns?
• MySQL query that returns a specific string if column is null?
• MySQL search if more than one string contains special characters?
• Display records with more than two occurrences in MySQL?
• What MySQL returns, if the length of the original string is greater than the length specified as an argument in LPAD() or RPAD() functions?
• Display the warning message when a FLOAT value is inserted into DECIMAL in MySQL?
• Display the default if element is not found in a C# List
• What happens if the substring is there for more than one time in the string given as the arguments of LOCATE() function?