Netezza Performance Server

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

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

Original Message:
Sent: Thu May 12, 2022 12:37 PM
From: David Briars
Subject: to_number template - Way to Set All '9's'?

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