CODEWITHSUNDEEP
mysqlsql
TNK
TNK

Reputation: 4333

String length limit to minimum characters with MYSQL

I need to select some subjects from db.. I used below code to select coma separated list of subjects. 

GROUP_CONCAT( DISTINCT s.subject_name SEPARATOR ', ') AS subjects,

its ok. its working for me. Its output is something like this.

Mathematics, Physiology, Business & Accounting Studies, Catholicism

Now I need to limit this string to 35 characters and need to add '...' at the end of the string.

It is something like this.

Mathematics, Physiology, Business...

So, can I know is it possible along with mysql when select the query?

Thank you.

Upvotes: 0

Views: 3882

Answers (4)

Lajos Arpad
Lajos Arpad

Reputation: 77073

Store your result in a variable and in an if clause check whether it is longer than 32 characters. If so select its first 32 characters and concatenate '...' to it.

Upvotes: 0

Taryn
Taryn

Reputation: 247870

You can use a CASE expression that will check the LENGTH() of the subjects. If it is longer than 35 characters then you can apply SUBSTRING() and CONCAT() to get the new value with the ... at the end:

select  
   case 
     when length(subjects) > 35
     then concat(substr(subjects, 1, 35), '...')
     else subjects end as subjects
from
(
  select GROUP_CONCAT( DISTINCT subject_name SEPARATOR ', ') AS subjects
  from yourtable
) src

See SQL Fiddle with Demo.

This returns:

|                               SUBJECTS |
------------------------------------------
| Mathematics, Physiology, Business &... |

Upvotes: 1

scones
scones

Reputation: 3355

just substring/concat it to the end of the result of group_concat

concat(substring(group_concat(distinct s.subject_name SEPARATOR ', '), 35), '...') AS subjects,

Usually it would be easier though, to do this in the application when using frameworks.

Upvotes: 0

John Woo
John Woo

Reputation: 263913

try something like this,

SELECT  IF(CHAR_LENGTH(subjects) > 35, CONCAT(LEFT(subjects,35), '...'), subjects),
        ...
FROM
(
    SELECT  GROUP_CONCAT( DISTINCT s.subject_name SEPARATOR ', ') AS subjects,
            ...
    FROM    tableName
    GROUP   BY ...
) s

I'd rather use CHAR_LENGTH than LENGTH when getting length of the character because...

Upvotes: 1

Related Questions