Oracle SQL, concatenate multiple columns + add text

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?

636857 次浏览

小开

Did you try the || operator ?

Concatenation Operator Documentation from Oracle >>>

小开

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

小开

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

小开

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

小开

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".

小开

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;