cancel
Showing results for 
Search instead for 
Did you mean: 

Keeping numeric format

Baron
Participant
1,020

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)

Accepted Solutions (1)

Accepted Solutions (1)

Breck_Carter
Participant

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

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

VolkerBarth
Contributor

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.

Answers (0)