I was trying to contract numbers from “1” to “1st”, 2 to “2nd” etc.
This amazing solution from Brad Schulz at
http://social.msdn.microsoft.com/Forums/en/transactsql/thread/6d176328-908c-4b00-8768-eb71b7c57e7b
did the job for me.
SELECT CAST(Number AS VARCHAR(10)) +CASE WHEN Number%10=1 AND Number%100<>11 THEN 'st' WHEN Number%10=2 AND Number%100<>12 THEN 'nd' WHEN Number%10=3 AND Number%100<>13 THEN 'rd' ELSE 'th' END FROM master..spt_values WHERE type='P' |