sqloracle-databaseto-char

Unwanted Spaces when concatenating fields and text strings

I'm putting the following in my SQL select statement to concatenate text strings (which are not fields in the database) with a couple database fields and I'm getting spaces where I try to use the to_char function to add leading zeros to a couple fields.

Running:

SELECT 'EP.'||TO_CHAR(PWROTPR_TRANSX_NBR,'0000000')||'.'||TO_CHAR(PWROTPR_SUBMIT_COUNTER,'00') as ATS_NBR

Yields: EP. 0017092. 01

How to I eliminate the unnecessary spaces?

Solution

  • Try using replace function:

    SELECT replace(('EP.'||TO_CHAR(PWROTPR_TRANSX_NBR,'0000000')||'.'||TO_CHAR(PWROTPR_SUBMIT_COUNTER,'00')), ' ', '') as ATS_NBR