asked on

Why can't I concatenate with a comma?

Why is that this SQL works:

select UPC || ',' || UPC from Products

but this doesn't:

select UPC || ',' from Products

I'm trying to select a set of UPCs with commas after them so I can paste them into the WHERE clause of another querying utility.

Sean Stuber

what doesn't work about it?

raises error? wrong results? never returns? keyboard catches fire?

DalTXColtsFan

ASKER

Good question.

It returns the UPCs without the commas.

But the first SQL statement returns the UPCs with the comma between them.

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DalTXColtsFan

ASKER

SQL Navigator was giving me those incorrect results. SQL*Plus gave me the correct results.

Sean Stuber

To eliminate whatever your tool you are using

try sqlplus.

I created my own products table and queried it with your selects, results were as I expected

what do you get with your table?
if you have many rows, add

WHERE rownum < 10

to your selects


SQL> create table products as
  2  select level upc from dual connect by level < 25;

Table created.

SQL> select UPC || ',' || UPC from Products;

UPC||','||UPC
----------------------------------------------------------------

1,1
2,2
3,3
4,4
5,5
6,6
7,7
8,8
9,9
10,10
11,11

UPC||','||UPC
----------------------------------------------------------------

12,12
13,13
14,14
15,15
16,16
17,17
18,18
19,19
20,20
21,21
22,22

UPC||','||UPC
----------------------------------------------------------------

23,23
24,24

24 rows selected.

SQL> select UPC || ',' from Products;

UPC||','
-----------------------------------------
1,
2,
3,
4,
5,
6,
7,
8,
9,
10,
11,

UPC||','
-----------------------------------------
12,
13,
14,
15,
16,
17,
18,
19,
20,
21,
22,

UPC||','
-----------------------------------------
23,
24,

24 rows selected.

SQL>

Open in new window

Sean Stuber

ah, you already had it. ok, glad you found it