# 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

• That's not possible with numbers. Format your numbers in the front-end. 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.

Monday, December 12, 2016 4:19 PM
• 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
GO
insert 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

Monday, December 12, 2016 4:36 PM

### All replies

• That's not possible with numbers. Format your numbers in the front-end. 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.

Monday, December 12, 2016 4:19 PM
• 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
GO
insert 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

Monday, December 12, 2016 4:36 PM