Data in oracle DB has trailing spaces but they're being stripped by sqlplus

Question

I'm retrieving data from an oracle database using sqlplus.

The command is something like this:

select property_name||'|'||property_value from some_table where x = 'foo'

and the data in the database definitely has trailing spaces (this is a thing which causes problems in an application I work with).

When I retrieve that data the spaces have been automatically trimmed somehow. I can see them when I use SQLDeveloper and when retrieved by the application directly.

Is there a way I can stop this happening?

No, trimspool is off. I'm wondering what would even happen to a line of data which itself had a linebreak in it. Would the linebreak be escaped, quoted, or mess up the data returned by adding an empty line? — AmbroseChapel
– AmbroseChapel, Commented May 17, 2018 at 4:21
@AmbroseChapel Adding an empty line can cause problems depending on which character you use. If you only use CHR(13), on some programs on some platforms (such as SQL*Plus on Windows), it will reset the cursor to the beginning of a line and will overwrite characters. — Jon Heller
– Jon Heller, Commented Jun 6, 2018 at 19:24

thatjeffsmith · Accepted Answer · 2018-05-17 14:59:17Z

1

Here is how it should be working.

SQL> create table spaces (blanks varchar2(20));

Table created.

SQL> insert into spaces values ('A');

1 row created.

SQL> insert into spaces values ('A     ');

1 row created.

SQL> insert into spaces values ('A  ');

1 row created.

SQL> Insert into SPACES (BLANKS) values ('A   

B   ');

SQL> commit;

Commit complete.


SQL> select blanks, length(blanks), blanks || '!' from spaces;

BLANKS               LENGTH(BLANKS) BLANKS||'!'
-------------------- -------------- ---------------------
A                    1               A!
A                    6               A    !
A                    3               A  !
A                    9               A
B                                    B  !

SQL>

The last column shows that none of the 'blanks' are being trimmed. Can you share your scenario in your question details? Or try what I've demonstrated and compare.

edited May 17, 2018 at 14:59

answered May 17, 2018 at 12:18

thatjeffsmith

22.7k6 gold badges44 silver badges140 bronze badges

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

1 Comment

AmbroseChapel Over a year ago

If I add the ` || '!' `to put the exclamation point after it, it works the way I see above. But if it's not concatenated with something on the right, the spaces aren't there.

Collectives™ on Stack Overflow

Data in oracle DB has trailing spaces but they're being stripped by sqlplus

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related