to_char pound sign question

[mode09]

We have a script that reads some table values and write out a data file to be uploaded into another system. For each field it is writing out, it is doing a to_char function on the value. If that value exceeds the specified in the format, it appears as pound signs (#) in the data file. The secondary system does not accept the # values, so they asked if it exceeds can it be converted to a different character. It is a fixed width field so I can't give them the full value, just the specified overflow characters ( right or wrong that is what they want). Is there a easy way to force the to_char to a different value if it is going to write out a #?

[Tim...]

Hi.

So this is you issue:

Code: Select all

CREATE TABLE t1 (
  data NUMBER
);

INSERT INTO t1 VALUES (1234567890);
INSERT INTO t1 VALUES (12345678901234567890);
COMMIT;

SELECT TO_CHAR(data, '9999999999') AS data
FROM   t1;

DATA
-----------
 1234567890
###########

2 rows selected.

SQL>

The following query replaces the "#"'s with "^".

Code: Select all

SELECT (CASE
          WHEN LENGTH(TO_CHAR(data)) > 10 THEN '^'
          ELSE TO_CHAR(data, '9999999999')
        END) AS data
FROM   t1;

DATA
-----------
 1234567890
^

2 rows selected.

SQL>

Another approach would be to translate the output.

Code: Select all

SELECT TRANSLATE(TO_CHAR(data, '9999999999'),'#','^') AS data
  2  FROM   t1;

DATA
-----------
 1234567890
^^^^^^^^^^^

2 rows selected.

SQL>

Cheers

Tim...