advice on to_number

Database
Enthusiast

advice on to_number

Hi

 

looking for some advice on converting strings to numbers

 

I understand to_number is an Oracle compatibility function, but it seems it has some undocumented behaviour

if string <= 40 chars it converts to number using all characters as expected

if string has between 41 and 64 chars, it returns a number with trailing zeros after either the 39th or 40th char

if string > 64 chars it returns null

if string > 128 chars it errors

Is this expected?  Its not documented in SQL Functions, Operators, Expressions, and Predicates 15.10 (2015)

 

I need a wrapper function that will:
1) accept a 'large' string and convert to a number if possible (we need to determine between numeric and non-numeric strings)

2) accept an optional format string (mainly to restrict to a string of a specific length)

 

This works, but is there any way to make the second argument optional?  We very rarely need to supply a format, but i'd like the same function to handle this .  Or do i need 2 separate functions?

 

replace function etl_load.safe_to_number (p_string varchar(4000), p_format varchar(50))
RETURNS number
LANGUAGE SQL
CONTAINS SQL
RETURNS NULL ON NULL INPUT
DETERMINISTIC
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN
case
when regexp_similar(p_string, '^[[:digit:].-]*$') = 1 then to_number(p_string, p_format)
end;

 

Thanks for any help

4 REPLIES
Junior Contributor

Re: advice on to_number


if string has between 41 and 64 chars, it returns a number with trailing zeros after either the 39th or 40th char

Can you show an example for this, I've never seen it.


if string > 128 chars it errors

if string > 64 chars it returns null

 I didn't know that, but I would never try to cast a VarChar(4000) to a number :)

 

Why don't you simply check the length instead of a RegEx?

case when char_length(p_string) <= 64 then to_number(p_string, p_format) end

 

Regarding the Format you can overwrite the function, i.e. the same name, but different parameters and a different SPECIFIC NAME.

Teradata Employee

Re: advice on to_number

Here is what I get:

On Teradata 15.10:

40-character number:
select to_number('1234567890123456789012345678901234567890');

1234567890123456789012345678901234567890

 

41-character number converts to float:
select to_number('12345678901234567890123456789012345678901');

1.2345678901234567890123456789012346E+40

 

64-character number also:
select to_number('1234567890123456789012345678901234567890123456789012345678901234');

1.2345678901234567890123456789012346E+63

 

65 characters exceeds the limit:
select to_number('12345678901234567890123456789012345678901234567890123456789012345');
?

 

Following dnoeth's suggestion, this works for me:

replace function safe_to_number (p_string varchar(4000), p_format varchar(50))
RETURNS number
SPECIFIC safe_to_number_f
LANGUAGE SQL
CONTAINS SQL
RETURNS NULL ON NULL INPUT
DETERMINISTIC
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN
case when char_length(p_string) <= 64 then to_number(p_string, p_format) else 0 end
;

replace function safe_to_number (p_string varchar(4000))
RETURNS number
SPECIFIC safe_to_number_nf
LANGUAGE SQL
CONTAINS SQL
RETURNS NULL ON NULL INPUT
DETERMINISTIC
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN
case when char_length(p_string) <= 64 then to_number(p_string) else 0 end
;

Highlighted
Teradata Employee

Re: advice on to_number

P.S. You don't have to call the SQL function "safe_to_number."  You can call it "to_number" and put it in the users' default database; then they will pick it up without changing their SQL.  If you want all users to use this, you can put it in the SYSLIB database; there it will override all invocations of to_number that do not specifically refer to TD_SYSFNLIB.to_number().

Teradata Employee

Re: advice on to_number

The behavior is documented but requires following several links...

 

to_number is documented to return NUMBER data type.

NUMBER data type is documented to store up to 38 digits accurately, sometime 39 or 40 depending on the number.

Following the link to default formatting, it is documented that the default format is FN9 unless the resulting string would be longer than 64 characters in which case it uses FNE.

 

All of the above behavior is consistent with the document.

 

The document does not appear to specify limitations on the input string - eg the cases described where the numeric string is >64 characters.