sql - Concat function is not working - invalid number of arguments

Question

I have a table with two columns(Name, Occupation). I want to output the value in a format something like this.

Jane(A) 
Jenny(D) 
Julia(A)

Hear First one is the name and the value in brackets is the first letter of their occupation.

So far what I have done is

SELECT CONCAT(Name,SUBSTR(Occupation,1,1)) FROM OCCUPATIONS;

which output value like this

JaneS 
JennyS 
JuliaD

to get the required format I tried this

SELECT CONCAT(Name,"(",SUBSTR(Occupation,1,1),")") FROM OCCUPATIONS;

then it's throwing an error something like this.

SELECT CONCAT(Name,'(',SUBSTR(Occupation,1,1),')') FROM OCCUPATIONS * ERROR at line 1: ORA-00909: invalid number of arguments

What is the mistake that I have done and what should I do to fix it.

Answer 1

SELECT CONCAT(Name,"(",SUBSTR(Occupation,1,1),")") FROM OCCUPATIONS;

First, the double quotes " are used to enclose identifiers. use single quote ' to wrap a string.

Second, CONCAT accepts two params.

You could nest bunch of concats, but it's easier and cleaner to use concatenation operation ||:

SELECT Name || '('  || SUBSTR(Occupation,1,1) || ')' FROM OCCUPATIONS;