Netezza Performance Server

 View Only
Expand all | Collapse all

to_number template - Way to Set All '9's'?

  • 1.  to_number template - Way to Set All '9's'?

    Posted Thu May 12, 2022 04:53 PM
    In the following query I am converting a string to a number with the 'to_number' function:

    select '1234567890',
    to_number('1234567890','9999999999')

    I created a template of 10 nines to define the largest possible length of the incoming field.

    Is there a way to say 'all 9's' without having to type in the many '9's.

    In my real case the incoming field is a  NVARCHAR(30), so I am hesitant to type in thirty '9's'.

    ------------------------------
    David Briars
    ------------------------------

    #NetezzaPerformanceServer


  • 2.  RE: to_number template - Way to Set All '9's'?

    Posted Fri May 13, 2022 09:24 AM
    Does following solve your issue.

    create table testtbl(a1 int, a2 varchar(10));

    insert into testtbl values(1, '1234567890');

    select to_number(a2, repeat('9', length(a2))) from testtbl;
    TO_NUMBER
    ------------
    1234567890
    (1 row)


    ------------------------------
    Kiran Kumar Gahlot
    ------------------------------