SAP SQL Anywhere

Question

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)

Accepted Answer

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

SAP SQL Anywhere

format mask to hide trailing and leading zeros

In Oracle 11gR2

E_STR

In PostgreSQL

Follow this question

Related questions