Syntax error MySQL query to add dots if string has more than 10 words?

MySQL query to add dots if string has more than 10 words?

For this, use CASE statement. Let us first create a table −

mysql> create table DemoTable
-> (
-> Title text
-> );
Query OK, 0 rows affected (0.61 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable values('My name is John and this is my first tutorial on MySQL');
Query OK, 1 row affected (0.11 sec)

mysql> insert into DemoTable values('This is Carol and I work on MongoDB');
Query OK, 1 row affected (0.19 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

Output

This will produce the following output −

+--------------------------------------------------------+
| Title                                                  |
+--------------------------------------------------------+
| My name is John and this is my first tutorial on MySQL |
| This is Carol and I work on MongoDB                    |
+--------------------------------------------------------+
2 rows in set (0.00 sec)

Following is the query to add dots if string has more than 10 words −

mysql> select (case when substring_index(tbl.Title, ' ', 10) = tbl.Title then tbl.Title
-> else concat(substring_index(tbl.Title, ' ', 10), '....................')
-> end) AS AddDots
-> from DemoTable tbl;

Output

This will produce the following output −

+-------------------------------------------------------------------+
| AddDots                                                           |
+-------------------------------------------------------------------+
| My name is John and this is my first tutorial.................... |
| This is Carol and I work on MongoDB                               |
+-------------------------------------------------------------------+
2 rows in set (0.00 sec)
Updated on: 2020-06-30T15:04:24+05:30

337 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements