CODEWITHSUNDEEP
sqloracle-database
user3157300
user3157300

Reputation: 21

Avoid Space between String and Sequence while concatenate

SQL> select 'TMP' || to_char(sq_id.nextval, '000000') res
  2    from dual
  3  /

RES
----------
TMP 000006

SQL> select concat('TMP',to_char(sq_id.nextval, '000000')) res
  2    from dual
  3  /

RES
----------
TMP 000007

Please Explain me how to avoid space between the above concatenated String

Upvotes: 2

Views: 308

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49102

It is because to_char(sq_id.nextval, '000000') produces a leading space in the output.

Let's see: 

SQL> CREATE SEQUENCE sq_id;

Sequence created.

SQL> select to_char(sq_id.nextval, '000000') from dual;

TO_CHAR
-------
 000001

You could use LTRIM to trim the leading space:

SQL> SELECT 'TMP' || ltrim(to_char(sq_id.nextval, '000000'), ' ') res FROM dual;

RES
----------
TMP000002

SQL>

Alternatively, you could use the FILL MODE format model.

Form documentation:

FM

"Fill mode". This modifier suppresses blank padding in the return value of the TO_CHAR function

For example,

SQL> SELECT 'TMP' || ltrim(to_char(sq_id.nextval, 'FM000000'), ' ') res FROM dual;

RES
----------
TMP000003

SQL>

Upvotes: 2

griffon vulture
griffon vulture

Reputation: 6774

To Avoid the space that is comming from to char method using FM (that is removing trailing spaces):

to_char(sq_id.nextval,'FM000000')

Upvotes: 1

Related Questions