format mask to hide trailing and leading zeros

[Former Member]

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)

[Former Member]

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 ?

[Breck_Carter · Accepted solution]

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