locked
Catenation RRS feed

  • 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 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
    Answerer
  • 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
    Answerer
  • 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