I want to construct an ID from a textual prefix and a number (more info here).

For example, I want to concatenate ABC with 1, while padding the 1 to 6 digits: ABC000001

However, when I try this, it puts a space between the two:

    'ABC' || to_char('1','000000') AS ID

Result: ABC 000001

Why does it put a space in there?

1 Answer 1


Because to_char() pads the result with spaces (I never understood why).

You need to use the "fill mode":

'ABC' || to_char('1','FM000000')
  • 3
    as per the documentation . docs.oracle.com/cd/B19306_01/server.102/b14200/… it's to provide room for the negative sign .. ;) "Negative return values automatically contain a leading negative sign and positive values automatically contain a leading space unless the format model contains the MI, S, or PR format element."
    – Ditto
    Commented Jun 28, 2017 at 19:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.