Question Re: Keeping numeric format in Technology Q&A

Keeping numeric format

Baron — Tue, 11 Jan 2022 06:58:56 GMT

Is it possible to keep the numerical format after concatenating with a string?

In the example below the first column remains correct, in the second column I lose the zero and the decimal comma is replaced with a dot.

select (1-0.4), 'blabla' || (1-0.4)

Re: Keeping numeric format

Breck_Carter — Tue, 11 Jan 2022 09:24:45 GMT

The server rules for converting a number to a string appear to be different than the rules used by the ISQL client to display a number.

select (1-0.4), STRING ( (1-0.4) ), STRING ( 0.6 ), 'blabla' || (1-0.4)
 1-.4 STRING((1-.4)) STRING(.6) 'blabla' || (1-.4) 
----- -------------- ---------- ------------------ 
  0.6 .6             .6         blabla.6

The first column 0.6 is not "a number converted to a string", it is "a number displayed by the ISQL client utility"

The second and third columns shows numbers that were converted to strings by the SQL Anywhere server.

If you want the leading zero to be included in the resulting string, you have to do that in your code... in the SELECT statement, or in your client application.

For example...

SELECT .6 AS number1,
       IF ABS ( number1 ) >= 1
          THEN STRING ( number1 ) 
          ELSE STRING ( 
             IF number1 < 0 THEN '-0' ELSE '0' ENDIF,
             STRING ( ABS ( number1 ) ) )
       ENDIF AS string1;

SELECT -.6 AS number1,
       IF ABS ( number1 ) >= 1
          THEN STRING ( number1 ) 
          ELSE STRING ( 
             IF number1 < 0 THEN '-0' ELSE '0' ENDIF,
             STRING ( ABS ( number1 ) ) )
       ENDIF AS string1;

number1 string1 
------- ------- 
    0.6 0.6     
(1 rows)


number1 string1 
------- ------- 
   -0.6 -0.6

Re: Keeping numeric format

Baron — Tue, 11 Jan 2022 10:38:00 GMT

Thanks Breck, I thought there is a cheaper way without own code.

Re: Keeping numeric format

VolkerBarth — Wed, 12 Jan 2022 03:41:51 GMT

Not unless SQL Anywhere will add a builtin number format function, as has been asked for often in the past.

See here for a sample function with thousand separators and decimal comma. FWIW there are several more samples in this forum.