Birkley Birkley - 5 months ago 10
SQL Question

Truncating Fields in MYSQL

Having an issue with field names not getting truncated. I'm displaying a leader board on a site, and players with long names are breaking the table. I tried the below, but to no avail. Hoping someone can help!

select
case
when char_length('player_name') >= 13 then left('player_name', 12) + '...'
else player_name
end player_name,
case
when char_length('squad') >= 10 then left('squad', 10)
else squad
end squad,
pub_rank
from
database.player_rank
order by pub_rank desc
limit 10;


Here is some sample data. I add row #'s in PHP. Some players won't have a squad.

player_name squad player_rank
Kalu 1st line 106.70
Jugu Cobra 96.29
BOG Blood 87.52
vai cavalo 87.09
Ebony&Ivory Bury 85.80
Taunt BlameTaunt 83.44
7461756e74207375636 82.98
Tut 82.03
baddreams 81.09
Tryo Team Gluten 80.40

`

Please and thanks!

Answer

Use backticks around your columns. Use concat() to concatenate the value and string.

select 
    case when char_length(`player_name`)>=13 
        then CONCAT(left(`player_name`, 12), '...') 
    else player_name end player_name, 

    case when char_length(`squad`)>=10 
        then left(`squad`, 10) 
    else squad end squad, 

pub_rank from database.player_rank 
order by pub_rank desc limit 10;

edit:

http://sqlfiddle.com/#!9/cc789/3 I built this fiddle with a query. Might have changed some names and used other numbers for the query, but it shows what has to be done -> concat() for concatenating and "`" for the tablenames