Prev: Technical Question
Next: Using SqlBytes/SqlChar write method in CLR
From: Aman on 15 Feb 2010 09:13
Hi All

I have a requirement in which I need to get numeric data, and return
it in the form of string but with subscript.

Eg: 1 -> 1st
2 -> 2nd
3 -> 3rd
4 -> 4th
21 -> 21st
25 -> 25th

Do we have such in-built function in MS SQL?

Please revert asap.
From: Plamen Ratchev on 15 Feb 2010 09:50
There is no built-in function for ordinals, but you can easily create one. Here is example:

CREATE TABLE Nums (
n INT NOT NULL PRIMARY KEY);

INSERT INTO Nums(n) VALUES(1);
INSERT INTO Nums(n) VALUES(2);
INSERT INTO Nums(n) VALUES(3);
INSERT INTO Nums(n) VALUES(4);
INSERT INTO Nums(n) VALUES(21);
INSERT INTO Nums(n) VALUES(25);

SELECT n,
CAST(n AS VARCHAR(10)) +
CASE WHEN n % 100 IN (11, 12, 13) THEN 'th'
WHEN n % 10 = 1 THEN 'st'
WHEN n % 10 = 2 THEN 'nd'
WHEN n % 10 = 3 THEN 'rd'
ELSE 'th'
END AS ordinal_num
FROM Nums
ORDER BY n;

/*

n ordinal_num
----------- ------------
1 1st
2 2nd
3 3rd
4 4th
21 21st
25 25th

*/

--
Plamen Ratchev
http://www.SQLStudio.com
From: TheSQLGuru on 15 Feb 2010 11:05
select case val when 1 then cast(val as varchar(10)) + 'st'

when 2 then cast(val as varchar(10)) + 'nd'

when 3 then cast(val as varchar(10)) + 'rd'

--...

end

from (select 1 as val union all select 2 union all select 3) as t


--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Aman" <amandeepthind(a)gmail.com> wrote in message
news:d5de4657-de55-43fc-9a16-df5a57b6805c(a)u5g2000prd.googlegroups.com...
> Hi All
>
> I have a requirement in which I need to get numeric data, and return
> it in the form of string but with subscript.
>
> Eg: 1 -> 1st
> 2 -> 2nd
> 3 -> 3rd
> 4 -> 4th
> 21 -> 21st
> 25 -> 25th
>
> Do we have such in-built function in MS SQL?
>
> Please revert asap.


From: Aman on 16 Feb 2010 02:37
On Feb 15, 7:50 pm, Plamen Ratchev <Pla...(a)SQLStudio.com> wrote:
> There is no built-in function for ordinals, but you can easily create one.. Here is example:
>
> CREATE TABLE Nums (
>   n INT NOT NULL PRIMARY KEY);
>
> INSERT INTO Nums(n) VALUES(1);
> INSERT INTO Nums(n) VALUES(2);
> INSERT INTO Nums(n) VALUES(3);
> INSERT INTO Nums(n) VALUES(4);
> INSERT INTO Nums(n) VALUES(21);
> INSERT INTO Nums(n) VALUES(25);
>
> SELECT n,
>         CAST(n AS VARCHAR(10)) +
>         CASE WHEN n % 100 IN (11, 12, 13) THEN 'th'
>              WHEN n % 10 = 1 THEN 'st'
>              WHEN n % 10 = 2 THEN 'nd'
>              WHEN n % 10 = 3 THEN 'rd'
>              ELSE 'th'
>         END AS ordinal_num
> FROM Nums
> ORDER BY n;
>
> /*
>
> n           ordinal_num
> ----------- ------------
> 1           1st
> 2           2nd
> 3           3rd
> 4           4th
> 21          21st
> 25          25th
>
> */
>
> --
> Plamen Ratchevhttp://www.SQLStudio.com

Hi Plamen
Thanks for the quick reply.
Above logic worked for me.
Cheers!
 | 
Pages: 1
Prev: Technical Question
Next: Using SqlBytes/SqlChar write method in CLR