on 2017 Aug 28 6:18 PM
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)
Request clarification before answering.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
75 | |
30 | |
9 | |
8 | |
7 | |
6 | |
6 | |
5 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.