Geek Answers Handbook

Merge rows in Oracle SQL without spacing?

I am trying to concatenate strings in oracle.

Below is my request:

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

Expected Result:

 c10000

But the output I get is the space between the "c" and the value 10000:

 c 10000

How to execute without spaces?

+8
string sql oracle oracle10g concatenation
source share
2 answers

This is not a problem with the concatenation operator, but with the to_char() function. Try instead:

 to_char(10000,'FM99999')

I quote the guide here :

FM .. Returns a value without leading or trailing spaces.

+20
source share

There are two solutions:

  1. Formatting Prefix Fill Mode (' FM '), which suppresses the optional blank character prefix for the to_char number in to_char . I believe this option is preferable because it is integrated with the to_char format and does not require an additional function call;
  2. LTRIM return value from to_char to_char .

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"

0
source share

More articles:


All Articles