TSQL-How to Contract Numbers

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' 
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s