Oracle SQL, concatenate multiple columns + add text

Question

So I basically wanna display this (whole row in ONE column):

I like [type column] cake with [icing column] and a [fruit column].

The result should be:

Cake_Column
----------------

I like chocolate cake with whipped_cream and a cherry.

I like strawberry cake with vanilla_cream and a lemon_slice.

etc.

etc.

I need some sort of TO_CHAR statement that does ([column] "some text" [column]) "new_column_name";

What am I supposed to know?

score 180 · Answer 1 · answered Oct 24 '09 at 22:15

180

You have two options for concatenating strings in Oracle:

CONCAT example:

CONCAT(
  CONCAT(
    CONCAT(
      CONCAT(
        CONCAT('I like ', t.type_desc_column), 
        ' cake with '), 
      t.icing_desc_column),
    ' and a '),
  t.fruit_desc_column)

Using || example:

'I like ' || t.type_desc_column || ' cake with ' || t.icing_desc_column || ' and a ' || t.fruit_desc_column

answered Oct 24 '09 at 22:15

OMG Ponies

325,700
82
523
502

This is really slow (to run, not really to type). Is there any better way? – Patrick Szalapski Oct 23 '15 at 18:34
2

This is so ugly, with respect to an very old-brand DBMS. How come Oracle don't suppoer many-argument-version Concat? However, thanks to Shankar, there's a || operator. – Scott Chu Aug 03 '17 at 02:31

score 93 · Answer 2 · edited Jan 02 '18 at 07:15

93

Did you try the || operator ?

Concatenation Operator Documentation from Oracle >>>

edited Jan 02 '18 at 07:15

AbsoluteƵERØ

7,816
2
24
35

answered Oct 24 '09 at 22:01

Shankar R10N

4,926
1
21
24

score 50 · Answer 3 · edited Jan 02 '18 at 07:12

50

select 'i like' || type_column || ' with' ect....

edited Jan 02 '18 at 07:12

shA.t

16,580
5
54
111

answered Oct 24 '09 at 22:02

Dani

14,639
11
62
110

3

Thank you so much for this answer. I like the '||' as it makes it easier to maintain the SQL query. – Jason TEPOORTEN Oct 25 '12 at 00:56

score 32 · Answer 4 · edited May 30 '13 at 06:47

32

Below query works for me @Oracle 10G ----

select PHONE, CONTACT, (ADDR1 ||  '-' || ADDR2 || '-' || ADDR3) as Address
from CUSTOMER_DETAILS
where Code='341';

O/P -

1111 abc@gmail.com 4th street-capetown-sa

edited May 30 '13 at 06:47

Rob

4,927
12
49
54

answered May 30 '13 at 06:31

Chaits

339
3
3

score 16 · Answer 5 · answered Jun 22 '18 at 12:31

The Oracle/PLSQL CONCAT function allows to concatenate two strings together.

CONCAT( string1, string2 )

string1

The first string to concatenate.

string2

The second string to concatenate.

E.g.

SELECT 'I like ' || type_column_name || ' cake with ' || 
icing_column_name || ' and a ' fruit_column_name || '.' 
AS Cake FROM table;

score 8 · Answer 6 · answered Aug 16 '16 at 16:50

8

Try this:

SELECT 'I like ' || type_column_name || ' cake with ' || 
icing_column_name || ' and a ' fruit_column_name || '.' 
AS Cake_Column FROM your_table_name;

It should concatenate all that data as a single column entry named "Cake_Column".

answered Aug 16 '16 at 16:50

SandPiper

2,816
5
30
52

Oracle SQL, concatenate multiple columns + add text

6 Answers6

Linked

Related