Oracle SQL Developer 3.1.07 Extra Spaces Between Characters Using Listagg

Oracle SQL Developer 3.1.07 extra spaces between characters using listagg

are you using UTF-16 + NVARCHAR2 by any chance? eg this:

SQL> select * from nls_database_parameters where parameter='NLS_NCHAR_CHARACTERSET';

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_NCHAR_CHARACTERSET         AL16UTF16

SQL> drop table test;

Table dropped.

SQL> create table test(a nvarchar2(10));

Table created.

SQL> insert into test values ('test');

1 row created.

SQL> insert into test values ('test 2');

1 row created.

SQL> select listagg(a, ',') within group (order by 1) from test group by 1;

LISTAGG(A,',')WITHINGROUP(ORDERBY1)
--------------------------------------------------------------------------------
 t e s t, t e s t   2

you could cast to a char to get round this. IF this is not acceptible, you need to raise a ticket with Oracle support.

SQL> select listagg(to_char(a),',') within group (order by 1) from test group by 1;

LISTAGG(TO_CHAR(A),',')WITHINGROUP(ORDERBY1)
--------------------------------------------------------------------------------
test,test 2

SQL>

Many extra spaces in front of value after accepting a number in oracle sql

The ttitle command has a format option:

FORMAT text

Specifies a format model that determines the format of following data items, up to the next FORMAT clause or the end of the command. The format model must be a text constant such as A10 or $999. See the COLUMN command for more information on formatting and valid format models.

You can change your ttile line to use the same format as your accept, pulling the substitution variable out of the main string and specifying the format with:

ttitle center 'Top ' &v_no format 99 ' Valuable Customer' skip 2

Then when passed 10 the output is:

                        Top 10 Valuable Customer

and when passed say 2 the output is:

                         Top 2 Valuable Customer

Incidentally, you might want to add set verify off at the start of the script, which will stop it showing the old and new output - that still has the extra spaces anyway, but you probably don't want to see that in your final output anyway.

Understanding ORACLE'S BETWEEN Keyword

The expression:

WHERE COL BETWEEN '11201' AND '111226'

is the same as:

WHERE COL >= '11201' AND COL <= '111226'

This returns nothing because -- as strings -- '11201' > '111226'. This uses alphabetic ordering, so this would be clearer if you used letters:

WHERE COL BETWEEN 'BBCAB' AND 'BBBCCG'

Clearly, there is nothing alphabetic between these values, because 'BBC' occurs after 'BBB'.

The moral? If you want comparisons that are intuitive, use the right types.

---

Related Topics