I wiold like to share with you about follwon Ordinal Number function script which include an integer input parameter.

SET

ANSI_NULLS ON

GO

SET

QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[FUNC_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 Test:

SELECT [dbo].[FUNC_OrdinalNumber](1)

Result: