[ 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 1
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
Answer 2
Try this
select if( char_length('player_name')>=13, left('player_name', 12) , player_name) from database.player_rank order by pub_rank desc limit 10;
And use one if for one condition