Concat function is not working - invalid number of

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

You can use just || for concatenating

select c1  ||   c2 ||   c3 || c4   as  col from mytable

Answer 2

This happens to be one reason why I prefer replace() over concat():

SELECT REPLACE(REPLACE('{Name} ({Occ})', '{Name}', Name'
                      ), '{Occ}', SUBSTR(Occupation, 1, 1)
              )

You can readily see the format of the string being created and easily change it. Also, REPLACE() converts arguments to the appropriate type (which Oracle does with string concatenation anyway).

Answer 3

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;

Answer 4

you may try this

select CONCAT(CONCAT(FIRST_NAME,' '),LAST_NAME) from employees;

By using this you have to give number of nested CONCAT functions equals to the number of arguments

Results

CONCAT(CONCAT(FIRST_NAME,''),LAST_NAME)

• Neena kochhar
• steven king
• Alexander Hunold