Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

Ordinal Function - SQL Server

RSS
Modified on Fri, Aug 26, 2011, 3:04 PM by Administrator Categorized as SQL Server
See also: Ordinal Function - C#

/*
select
     n
    ,result = dbo.Ordinal(n)
from
    dbo.GetCounterTable(50)
*/
create function dbo.Ordinal
    (
    @n int
    ) returns varchar(100) as begin

declare 
     @result varchar(100)
    ,@mod100 int
    ,@mod10  int

select
     @mod100    = @n % 100
    ,@mod10     = @n % 10
    ,@result    = convert(varchar(100), @n) + case
                    when @mod10 = 1 and @mod100 <> 11 then 'st'
                    when @mod10 = 2 and @mod100 <> 12 then 'nd'
                    when @mod10 = 3 and @mod100 <> 13 then 'rd'
                    else 'th'
                    end                      

return @result
end

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.