Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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)

asked 11 Jan '22, 06:58

Baron's gravatar image

Baron
2.1k138151178
accept rate: 48%


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

answered 11 Jan '22, 09:24

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

edited 11 Jan '22, 09:28

2

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

(11 Jan '22, 10:38) Baron
Replies hidden
1

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.

(12 Jan '22, 03:41) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×17
×9
×6
×5

question asked: 11 Jan '22, 06:58

question was seen: 624 times

last updated: 12 Jan '22, 03:41