SQL Language (MOSC)

MOSC Banner

How do I return leading zeros?

edited Aug 1, 2013 4:42AM in SQL Language (MOSC) 9 commentsAnswered ✓
Hello everyone,

I'm trying to duplicate the functionality of this case statement in 10.2.0.5 on Solaris SPARC 64:

case when substr(to_char(COLUMN),1,1) = '.'

                then '0'||to_char(COLUMN)

                when substr(to_char(COLUMN),1,2) = '-.'

                then replace(to_char(COLUMN),'-.','-0.')

                else to_char(COLUMN) end COLUMN

For the particular problem I'm trying to solve, the COLUMN column may be in the following formats:

NUMBER(17,7)

NUMBER(14,4)

NUMBER

The actual data may have a wide variety of digits to the left and right of the decimal point.

I"m aware of the fact that TO_CHAR provides a format argument but I have not been able to create a format pattern that matches this exact functionality.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center