Mysql – ny way to concatenate a value in a column with different strings

concatMySQL

enter image description here

As you can see on the column 'Race Position' I have a set of values corresponding to the participants results. 1 being 1st Position and so on.

What I wanted was to be able to concat the values and give the first 3 strings as so; 1'st', 2'nd', 3'rd'. The position after will all finish with 'th' at the end for example 4th, 5th, 6th ,7th and so on.

Is there any way to implement this? I have used a cross join in order to make my positions work.

Best Answer

I have the generic expression you need:

SELECT CONCAT(@num,IF(MOD(@num,100) IN (11,12,13),'th',
IF(MOD(@num,10) IN (1,2,3),SUBSTR('stndrd',2*MOD(@num,10)-1,2),'th')));

What this does is the following

  • If the last digit of a number is 1 (other than last two digits being 11), st is appended
  • If the last digit of a number is 2 (other than last two digits being 12), nd is appended
  • If the last digit of a number is 3 (other than last two digits being 13), rd is appended
  • All other two-digit combinations have th appended

To test this out, run the following

SELECT CONCAT(num,IF(MOD(num,100) IN (11,12,13),'th',
IF(MOD(num,10) IN (1,2,3),SUBSTR('stndrd',2*MOD(num,10)-1,2),'th'))) n FROM
(SELECT th*1000+h*100+t*10+u num FROM
(SELECT 0 th UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) TH,
(SELECT 0 h UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) H,
(SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) T,
(SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) U) A
ORDER BY num;

UPDATE 2017-04-13 14:29 EDT

You asked me

Hey Rolando, I understand roughly how you have implemented this. But How would I work this around my code as so; select id, duration, @position := @position + 1 as position from (select @position := 0) x, (select id, duration from course order by duration asc) y;

SELECT id,CONCAT(position,IF(MOD(position,100) IN (11,12,13),'th',
IF(MOD(position,10) IN (1,2,3),SUBSTR('stndrd',2*MOD(position,10)-1,2),'th')))
FROM 
(
    select id, duration, @position := @position + 1 as position
    from (select @position := 0) x,
    (select id, duration from course order by duration asc) y
) a;

Basically, I placed your query inside a subquery and applied the generic expression.

Related Question