Hi There, I have an SQL problem I hope someone would be able to assist.

I need to covert a number to string with a specific format, i.e. not ending up the number with .0000 which doesn't make sense. Is there any function in SQL Anywhere that gets a number and a format mask and produces the required string?

Another one, regarding alternative to NVL function of Oracle. If I use the nullif in Strings, I still get the result as number which then get's converted. Any ideas how to prevent that?

Thanks, Dvir

asked 14 Feb '12, 09:38

XDvir's gravatar image

XDvir
16112
accept rate: 0%

edited 15 Mar '13, 19:14

Mark%20Culp's gravatar image

Mark Culp
22.7k9129266

2

COALESCE or ISNULL are SQL Anywhere's alternative to NVL, not NULLIF.

(14 Feb '12, 10:55) Breck Carter

AFAIK, there's no such general "number-formatting function" (though it has been requested in the past...). However, there are some general data conversion functions like STR or CAST (or conversions to numerical data with fixed number of decimal places) that may be helpful in simple cases, such as

select str(6.1234), cast(6.1234567890 as varchar),
   cast(cast(6.1234567890 as numeric(10,2)) as varchar)

returns

6, 6.123456789, 6.12

The following FAQ may help further:

permanent link

answered 14 Feb '12, 10:12

Volker%20Barth's gravatar image

Volker Barth
29.9k294446654
accept rate: 32%

edited 14 Feb '12, 10:13

the convert function will take a number and convert it to text. select convert( char( 20 ), 12345.6789 ) from table will convert the number to text but will not format it to 12,345.6789

permanent link

answered 15 Feb '12, 13:35

Tom%20Mangano's gravatar image

Tom Mangano
587182434
accept rate: 6%

AFAIK, "convert(char, myNumber)" is semantically identical to "cast(myNumber as char)" - i.e. as long as you don't use convert() with the optional "format-style" parameter, both work similar...

(15 Feb '12, 13:40) 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:

×25
×4

question asked: 14 Feb '12, 09:38

question was seen: 3,260 times

last updated: 15 Mar '13, 19:14