CODEWITHSUNDEEP
sqloracle-database
agent provocateur
agent provocateur

Reputation: 830

SQL with Oracle - Special Symbols

I have a SELECT statement that was written to be used with SQLPLUS with an Oracle DB.
The code works perfectly fine with SQLPlus.

I am trying to understand what this SELECT statement means:

SELECT '"' || a1 || '","' || a2 || '","' || b1 || '","' || c1 || '","' || c2 || '","' || c3 || '","' || sum(d1) || '",'
FROM ...
WHERE ...
GROUP BY ...
ORDER BY ...


Specifically, I am trying to understand what the following sequence of symbols means:

||

&

'"'

&

'","'

&

'",'

Why does the SELECT statement start with '"' ||?
Why does the SELECT portion end with a comma?

What are they for/what are they doing?
What do they equate to?
Where is this from? Is this native to SQLPlus or Oracle? I couldn't find any documentation that described this at all. I have tried MANY google searches but seeing as how I have no idea what they were (and consequently what to search for), it is very hard to find anything on it.

Upvotes: 0

Views: 164

Answers (2)

Up_One
Up_One

Reputation: 5271

See bellow

  • || will concatenate strings + data

  • ' " ' - will print "

  • ' "," ' - will print ","

  • ' ", ' - will print ",

    Example :


    SQL> select 
    '>>'|| 
    DESCRIPTION_PARAMETER ||
    ' string ' ||
    DESCRIPTION_PARAMETER ||
    ' $%&* '
     from parameters_companies;
    
Examples output
======================
>>BASE UTC string BASE UTC $%&*<br>
>>WARRANTY string WARRANTY $%&*<br>
>>BASE UTC string BASE UTC $%&*<br>

SQL>

Upvotes: 1

user275683
user275683

Reputation:

In ORACLE || is used to concatenate strings together, it is the same as SQL-Server +

'"' & '","' & '",' is just part of the strings. It is only meant for showing up on output but does not have any meaning.

here is documentation on Oracle Concatenation Operator http://docs.oracle.com/cd/B19306_01/server.102/b14200/operators003.htm

Why does the SELECT statement start with '"' ||?

Just like it was mentioned in comments it is done to surround column with " on both sides

Why does the SELECT portion end with a comma?

That is strange, I would expect it to end with some other character to identify end of the line.

What are they for/what are they doing?

It is likely that it is special formatting for CSV file.

Upvotes: 1

Related Questions