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”