This forum is currently locked. You can't register or post questions at this time. (read more)

to_char pound sign question

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

mode09
Member
Posts: 17
Joined: Wed Jul 16, 2008 3:19 pm

to_char pound sign question

Postby mode09 » Mon Feb 20, 2012 2:51 pm

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...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: to_char pound sign question

Postby Tim... » Mon Feb 20, 2012 3:52 pm

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 0 guests

cron