Ordinal Numbers in T-SQL

I wanted to write a query that took numbers and turned them into ordinal numbers – re: 1 = 1st, 2 = 2nd, 3 = 3rd, etc.

I took this code from Chip Pearson’s web site. His posting was about ordinal numbers in Excel. I converted the code to a T-SQL function, and I can now use it in any other T-SQL queries I write. Here it is:

create Function dbo.OrdinalNumber
/* used to return an ordinal number */
(@Number integer)
returns varchar(6)
as begin

declare @N int
declare @Suffix char(18)
declare @Result varchar(6)

select @N = @Number%100
select @Suffix = ‘stndrdthththththth’

If ((Abs(@N) >= 10) And (Abs(@N) <= 19))
Or ((Abs(@N) % 10) = 0)
begin
select @Result= cast(@Number as varchar) + ‘th’
end
else
begin
select @Result = cast(@Number as varchar) + substring(@Suffix,
((Abs(@N) % 10) * 2) – 1, 2)
end
return @Result
end

To use it just feed it the number you want to convert.

select dbo.ordinalnumber(1)
will return “1st”

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s