INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

TO_CHAR(Number, '000') and a Space

TO_CHAR(Number, '000') and a Space

(OP)
If I have a table with 2 fields:

VARCHAR2    NUMBER
MY_NAME    MY_NUMBER
ABCD           15
 
And user wants to see as an outcome:
MyField
ABCD-015


I should do:
Select MY_NAME || '-' || TO_CHAR(MY_NUMBER, '000') As MyField

But this way I get:
ABCD- 015 
With the space between the – and 015

To eliminate the space, I know I can do this:
Select MY_NAME || '-' || TRIM(TO_CHAR(MY_NUMBER, '000')) As MyField

But I should not need to do it, right?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: TO_CHAR(Number, '000') and a Space

The fill mode modifier of the to_char function eliminates the space:

CODE --> SQL

Select MY_NAME || '-' || TO_CHAR(MY_NUMBER, 'fm000') As MyField 

RE: TO_CHAR(Number, '000') and a Space

(OP)
Thank you, I was not aware of the fm
It looks like ths '-' is creating this problem

So what would be the "preferred" way? TRIM or 'fm000'?
Since both give the same outcome.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: TO_CHAR(Number, '000') and a Space

The '-' does not matter. to_char always right-justifies numbers unless told otherwise.
TRIM is easier to understand, but fm000 is better performance-wise (a context switch less per call), so the preferred way depends on your goal.
If you give this code to someone who will maintain and modify it him-/herself use TRIM, but if you use this as part of a procedure processing lots of records on a busy system use fm000 (and place a comment somewhere that explains whats going on).
I usually go the fm000 + comment way :)

RE: TO_CHAR(Number, '000') and a Space

(OP)
I was also thinking about Replace(xxx, ' ', '') (replace a space with an empty space) but since I want to use this logic in creating a View out of a few tables, 'fm000' with a comment is the way to go.

Thank you.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: TO_CHAR(Number, '000') and a Space

Without the fm, the TO_CHAR() outputs a fixed-width result wide enough to display all possible values of my_number, within the context of the format mask you specify. This means (unless you explicitly include a sign in your format mask) it will add a space to the front of the number to make room for a minus sign if my_number is negative.

The fm tells Oracle to only make the string as wide as it needs to be, i.e. to trim out that leading space for positive numbers.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close