TO_CHAR(Number, '000') and a Space 2

[Andrzejek]

If I have a table with 2 fields:

[pre]
VARCHAR2 NUMBER
MY_NAME MY_NUMBER
ABCD 15
[/pre]
And user wants to see as an outcome:[tt]
MyField
ABCD-015[/tt]

I should do:[tt]
Select MY_NAME || '-' || TO_CHAR(MY_NUMBER, '000') As MyField
[/tt]
But this way I get:[pre]
ABCD- 015[/pre]
With the space between the – and 015

To eliminate the space, I know I can do this:[tt]
Select MY_NAME || '-' || [blue]TRIM([/blue]TO_CHAR(MY_NUMBER, '000')[blue])[/blue] As MyField
[/tt]
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.

 

[stefanhei]

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

Select MY_NAME || '-' || TO_CHAR(MY_NUMBER, '[COLOR=#A40000]fm[/color]000') As MyField

 

[Andrzejek]

Thank you, I was not aware of the fm
It looks like ths [tt]'-'[/tt] 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.

 

[stefanhei]

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

 

[Andrzejek]

I was also thinking about [tt]Replace(xxx, ' ', '')[/tt] (replace a space with an empty space) but since I want to use this logic in creating a View out of a few tables, [tt]'fm000'[/tt] 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.

 

[ChrisHunt]

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