Netezza Performance Server

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

    Posted Thu May 12, 2022 04:53 PM
    In the query below is there a way to specify all nines, without typing in ten nines?  


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

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


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

    Posted Fri May 13, 2022 09:20 AM
    Alternatives for your consideration.  Just recast the column to the datatype you desire.

    e.g.
    your_column::BIGINT
    your_column::NUMERIC(20,0)
    cast(your_column as BIGINT)

    ------------------------------
    MARK Fraase
    ------------------------------



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

    Posted Fri May 13, 2022 09:24 AM
    Does following solve your problem?

    SYSTEM.ADMIN(ADMIN)=> create table testtbl(a1 int, a2 varchar(10));
    CREATE TABLE
    SYSTEM.ADMIN(ADMIN)=> insert into testtbl values(1, '1234567890');
    INSERT 0 1
    SYSTEM.ADMIN(ADMIN)=> select to_number(a2, repeat('9', length(a2))) from testtbl;
    TO_NUMBER
    ------------
    1234567890
    (1 row)

    --
    Kiran

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



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

    Posted Wed May 18, 2022 04:34 PM
    Thank you Mark and Kiran.  

    Exactly what I needed.  

    -Dave

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