Concatenate strings in Oracle SQL without a space in between?

Question

I am trying to concatenate strings in oracle.

The following is my query:

insert into dummy values('c'||to_char(10000,'99999'));

The expected result is:

c10000

But the output I get is with a space in between 'c' and the value 10000:

c 10000

How to concat without spaces?

Erwin Brandstetter · Accepted Answer · 2011-10-30 14:06:10Z

21

This is not an issue with the concatenation operator but with the function to_char(). Try instead:

to_char(10000,'FM99999')

I quote the manual here:

FM .. Returns a value with no leading or trailing blanks.

answered Oct 30, 2011 at 14:06

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

PaulK · Accepted Answer · 2019-06-05 17:05:12Z

There are two solutions:

Fill Mode ('FM') formatting prefix that suppresses the additional blank character prefix for the to_char number conversion. I suggest this one is preferred, because it is integrated with the to_char format and does not require an additional function call;
LTRIM of the returned value from the to_char number conversion.

The code below shows the results of both solutions:

Select concat('NTA', to_char(1,'FM0000000000000')), 
concat('NTA', ltrim(to_char(1,'0000000000000'))), 
concat('NTA', to_char(1,'0000000000000'))
from dual;

"CONCAT('NTA',TO_CHAR(1,'FM0000000000000'))": "NTA0000000000001" "CONCAT('NTA',LTRIM(TO_CHAR(1,'0000000000000')))": "NTA0000000000001" "CONCAT('NTA',TO_CHAR(1,'0000000000000'))": "NTA 0000000000001"

Collectives™ on Stack Overflow

Concatenate strings in Oracle SQL without a space in between?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related