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',

    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


  • 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;
    (1 row)

    Kiran Kumar Gahlot