Concatenation ?

[acct98]

What happens to the leading or trailing spaces when you concatenation two fields?

 

[carp]

acct98 -
This is a pretty simple experiment to set up:


16:24:27 SQL> create table test1(c1 varchar2(10), c2 varchar2(5), c3 char(10), c4 char(5));

Table created.

16:24:34 SQL> insert into test1 values(' a ',' b ',' c ',' d ');

1 row created.

16:28:16 SQL> select REPLACE(c1||c2||c3||c4,' ','^') from test1;
^^^a^^^^b^^^^c^^^^^^^d^^^

Elbert, CO
1629 MST

 

[acct98]

This is what I was looking for. The concatenation function was removing the space which is vital for this statement to work.


SELECT ACCT_STRING,LASTNAME, FIRSTNAME
FROM x
where STN = '101'
AND rpad(SUBSTR(ACCT_STRING,20,6),6,' ')||rpad(SUBSTR(ACCT_STRING,16,2),4,' ')||rpad(SUBSTR(ACCT_STRING,31,4),4,' ')
NOT IN ('00000140 04 ')

 

[SantaMufasa]

Acct,

My intent is not to split hairs but, instead, to clarify. First, (and this is immaterial to you objective) concatenation is not a function, it is an operation. Second, concatenation does not, in and of itself, cause "trimming" (or "removing the space&quot on either end. If there are leading or trailing spaces, concatenation does not remove them; if such spaces are disappearing, something else is causing that to happen.

Are you still suffering the symptoms, or did Carp's post resolve your need?

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:00 (06Feb04) GMT, 10:00 (06Feb04) Mountain Time)