CODEWITHSUNDEEP
oracle-databaseconcatenation
Nikolas
Nikolas

Reputation: 2432

How to avoid to_char() cast in concatenation oracle?

How to avoid to_char() cast with formatting fractional value to avoid possible errors in future depending on input values? e.g.

SELECT -0.56 || 'value' FROM DUAL;

will return

-.56value

and I would like to have

-0.56value.

I know that I can use 

select to_char(-0.56, '990.00') || 'value' from dual;

But I don't know how many digits can be in the future

Upvotes: 0

Views: 272

Answers (2)

Vecchiasignora
Vecchiasignora

Reputation: 1315

try this

select rtrim(to_char(-0.56, 'FM9999999990.999999999'), '.') || 'value' from dual;

Upvotes: 1

user5683823
user5683823

Reputation:

You can use to_char(-0.56, '999999990.00') with enough 9's to cover all your possible values. (There is always a limit, since the NUMBER data type is at most 38 digits - and yes, you could have 37 9's and a 0 in the format model.)

This may add some unwanted spaces to the left; to modify that behavior, use the fm modifier: to_char(-0.56, 'fm99999999990.00').

Upvotes: 2

Related Questions