oracle Concat 函数不起作用 - 参数数量无效

Concat function is not working - invalid number of arguments

提问by Gurwinder Singh

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

SELECT CONCAT(Name,'(',SUBSTR(Occupation,1,1),')') FROM OCCUPATIONS * 第 1 行出错：ORA-00909：参数数量无效

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

我犯了什么错误，我应该怎么做才能解决它。

回答by Gurwinder Singh

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

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, CONCATaccepts two params.

其次，CONCAT接受两个参数。

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

您可以嵌套一堆连接，但使用连接操作更容易、更干净||：

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

回答by Gordon Linoff

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

这恰好是一个原因，我更喜欢replace()在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).

您可以很容易地看到正在创建的字符串的格式并轻松更改它。此外，REPLACE()将参数转换为适当的类型（Oracle 无论如何都会使用字符串连接）。

回答by Anmol Mourya

You can use just || for concatenating

你可以只使用 || 用于连接

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

回答by SKP_144

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

通过使用它，您必须给出嵌套 CONCAT 函数的数量等于参数的数量

Results

结果

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

• Neena kochhar
• steven king
• Alexander Hunold

• 尼娜·科查尔
• 史蒂文金
• 亚历山大·胡诺德