[ 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