Answered by:
Catenation
Question

One of the columns of my table has single or double digit numbers. I want to automatically append the strings "st", "nd","rd" and "th" to the end of numbers that end in 1, 2, 3 and any other number, respectively. Please I need you help.Monday, December 12, 2016 4:13 PM
Answers

That's not possible with numbers. Format your numbers in the frontend. Otherwise you can use
WITH B1 ( n ) AS ( SELECT 1 UNION ALL SELECT 1 ), B2 ( n ) AS ( SELECT 1 FROM B1 A , B1 B ), B3 ( n ) AS ( SELECT 1 FROM B2 A , B2 B ), B4 ( n ) AS ( SELECT 1 FROM B3 A , B3 B ), Numbers ( n ) AS ( SELECT ROW_NUMBER() OVER ( ORDER BY n ) FROM B4 ) SELECT CAST(n AS NVARCHAR(255)) + CASE n WHEN 1 THEN N'st' WHEN 2 THEN N'nd' WHEN 3 THEN N'rd' ELSE N'th' END FROM Numbers;
But then you don't have no longer numbers. Proposed as answer by Ed Price  MSFTMicrosoft employee Thursday, December 15, 2016 5:51 AM
 Marked as answer by Ed Price  MSFTMicrosoft employee Tuesday, April 10, 2018 11:06 PM
Monday, December 12, 2016 4:19 PMAnswerer 
You will need a trigger for this, like this:
use tempdb
GO
drop table numbers
create table numbers(pk int identity primary key, number varchar(20))
GO
create trigger trg_numbers on Numbers
for update, insert
as
update numbers set number = case
when right(i.number,1)='1' then i.number +'st'
when right(i.number,1)='2' then i.number +'nd'
when right(i.number,1)='3' then i.number +'rd'
else i.number+'th' end
from numbers
join inserted i on i.pk=numbers.pk
GOinsert into numbers(number) values(1)
insert into numbers(number) values(2)
insert into numbers(number) values(3)
insert into numbers(number) values(4)
insert into numbers(number) values(5)
insert into numbers(number) values(6)
insert into numbers(number) values(7)
insert into numbers(number) values(8)
insert into numbers(number) values(9)
insert into numbers(number) values(10)
insert into numbers(number) values(11)
insert into numbers(number) values(12)
select * from numbers Proposed as answer by Ed Price  MSFTMicrosoft employee Thursday, December 15, 2016 5:51 AM
 Marked as answer by Ed Price  MSFTMicrosoft employee Tuesday, April 10, 2018 11:06 PM
Monday, December 12, 2016 4:36 PM
All replies

That's not possible with numbers. Format your numbers in the frontend. Otherwise you can use
WITH B1 ( n ) AS ( SELECT 1 UNION ALL SELECT 1 ), B2 ( n ) AS ( SELECT 1 FROM B1 A , B1 B ), B3 ( n ) AS ( SELECT 1 FROM B2 A , B2 B ), B4 ( n ) AS ( SELECT 1 FROM B3 A , B3 B ), Numbers ( n ) AS ( SELECT ROW_NUMBER() OVER ( ORDER BY n ) FROM B4 ) SELECT CAST(n AS NVARCHAR(255)) + CASE n WHEN 1 THEN N'st' WHEN 2 THEN N'nd' WHEN 3 THEN N'rd' ELSE N'th' END FROM Numbers;
But then you don't have no longer numbers. Proposed as answer by Ed Price  MSFTMicrosoft employee Thursday, December 15, 2016 5:51 AM
 Marked as answer by Ed Price  MSFTMicrosoft employee Tuesday, April 10, 2018 11:06 PM
Monday, December 12, 2016 4:19 PMAnswerer 
You will need a trigger for this, like this:
use tempdb
GO
drop table numbers
create table numbers(pk int identity primary key, number varchar(20))
GO
create trigger trg_numbers on Numbers
for update, insert
as
update numbers set number = case
when right(i.number,1)='1' then i.number +'st'
when right(i.number,1)='2' then i.number +'nd'
when right(i.number,1)='3' then i.number +'rd'
else i.number+'th' end
from numbers
join inserted i on i.pk=numbers.pk
GOinsert into numbers(number) values(1)
insert into numbers(number) values(2)
insert into numbers(number) values(3)
insert into numbers(number) values(4)
insert into numbers(number) values(5)
insert into numbers(number) values(6)
insert into numbers(number) values(7)
insert into numbers(number) values(8)
insert into numbers(number) values(9)
insert into numbers(number) values(10)
insert into numbers(number) values(11)
insert into numbers(number) values(12)
select * from numbers Proposed as answer by Ed Price  MSFTMicrosoft employee Thursday, December 15, 2016 5:51 AM
 Marked as answer by Ed Price  MSFTMicrosoft employee Tuesday, April 10, 2018 11:06 PM
Monday, December 12, 2016 4:36 PM