Is there a way I can convert a numeric, with scale, to a string with leading and trailing zeros stripped ?

I've looked at the CAST and CONVERT documentation, but cannot find a mask to apply to a numeric.

In PostgreSQL and Oracle I simply apply a format mask, the same mask works for both, like so:

In Oracle 11gR2

SQL> select e from t3;

 E

.1
.2
.3
.4

SQL> DESC T3
Name Null? Type


A NUMBER(38) B VARCHAR2(10) D DATE E NUMBER(4,4)

SQL> select to_char(e,'FM9.9999') as e_str from t3;

E_STR

.1 .2 .3 .4

In PostgreSQL

ft_node=# \d+ t3 Table "public.t3" Column | Type | Modifiers | Storage | Stats target | Description --------+-----------------------+-----------+----------+--------------+------------- a | numeric | | main | | b | character varying(10) | | extended | | d | date | | plain | | e | numeric(4,4) | | main | |

ft_node=# select to_char(t3.e,'FM9.99999') as e_str from t3; e_str


.1 .2 .3 .4 (4 rows)

asked 28 Aug, 17:18

bluefrog's gravatar image

bluefrog
183118
accept rate: 0%

I think this is somewhat of a solution, although it seems convoluted,

select cast ( convert(double,e) as varchar) as  e_str from t3

Any suggestions for alternatives maybe ?

(29 Aug, 16:49) bluefrog

If you use Oracle's TO_CHAR with the format model feature a lot, perhaps you should implement your own version of (a subset of?) TO_CHAR as a SQL Anywhere CREATE FUNCTION.

Otherwise, here is a combination of SQL Anywhere's STRING(), REPLACE() and TRIM() functions that (a) changes all zeros to spaces, (b) removes leading and trailing spaces, then (c) changes all surviving spaces back to zeros...

( Caveat Emptor: The testing you see is the testing that was performed :)

CREATE TABLE T3 ( E NUMERIC(4,4) );
INSERT T3 VALUES (.1), (.2), (.3), (.4), (.501), (.6101);
COMMIT;
SELECT E, 
       REPLACE ( TRIM ( REPLACE ( STRING ( E ), '0', ' ' ) ), ' ', '0' ) AS E_STR
  FROM T3 
 ORDER BY E;

E,E_STR
0.1000,.1
0.2000,.2
0.3000,.3
0.4000,.4
0.5010,.501
0.6101,.6101
permanent link

answered 29 Aug, 17:45

Breck%20Carter's gravatar image

Breck Carter
27.0k444614889
accept rate: 21%

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:

×6
×5
×3

question asked: 28 Aug, 17:18

question was seen: 113 times

last updated: 29 Aug, 17:45