oracle 如何使用 SQL CONCAT/SUBSTR？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28583448/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use SQL CONCAT/SUBSTR?
提问by ChantelleL
I am trying to create a username for each person in the marketing table. I want an update statement that inserts the new username into the username column.
我正在尝试为营销表中的每个人创建一个用户名。我想要一个将新用户名插入用户名列的更新语句。
I want to take the first letter of the firstname, join to the first 5 letters of the surname, and save this into the username column of the marketing table. I need to put this inside a procedure somehow too.
我想取firstname的第一个字母，加入姓氏的前5个字母，并将其保存到marketing表的username列中。我也需要以某种方式将它放入一个程序中。
heres my code:
继承人我的代码：
SELECT CONCAT(m.firstname, 1)||SUBSTR(m.surname, 5)
INTO username
FROM marketing
WHERE marketing_id = 10001;
However, I am getting errors that I cannot seem to fix, like firstname and surname are invalid identifiers. Any help here is appreciated.
但是，我遇到了似乎无法修复的错误，例如名字和姓氏是无效的标识符。任何帮助在这里表示赞赏。
采纳答案by koushik veldanda
update username
set username= (select concat(left(firstname,1),left(surname,5))
from marketing
WHERE marketing_id = 10001)
WHERE marketing_id = 10001;
回答by Alex Poole
You seem to be confusing the concatfunction, substrfunction and the concatentation operator (||). You aren't using substrto get the first character of the first name, and to restirct the length of both you need to provide both the starting position and the substring length.
您似乎混淆了concat函数、substr函数和连接运算符 ( ||)。您不使用substr获取名字的第一个字符，并重新定义两者的长度，您需要提供起始位置和子字符串 length。
You're also referring to m.firstnamewithout defining mas an alias for your table name; an alias isn't really needed here as there is only one table but if you use it, use it consistently.
您还指的是m.firstname没有定义m表名的别名；这里并不真正需要别名，因为只有一张表，但如果您使用它，请始终如一地使用它。
To get the first letter of the firstname and the first five letters of the surname you could use:
要获取名字的第一个字母和姓氏的前五个字母，您可以使用：
SELECT SUBSTR(m.firstname, 1, 1) || SUBSTR(m.surname, 1, 5)
FROM marketing m
WHERE m.marketing_id = 10001;
or
或者
SELECT CONCAT(SUBSTR(m.firstname, 1, 1), SUBSTR(m.surname, 1, 5))
FROM marketing m
WHERE m.marketing_id = 10001;
If you're updating a column in the same table though, rather than using a PL/SQL intoclause, you need an update not a select:
但是，如果您要更新同一个表中的列，而不是使用 PL/SQLinto子句，则需要更新而不是选择：
UPDATE marketing
SET username = SUBSTR(firstname, 1, 1) || SUBSTR(surname, 1, 5)
WHERE marketing_id = 10001;
回答by B98
The min m.nameneeds to have been defined, that's missing. CONCAT(MySQL?) should consume strings, you pass a number (1) in addition to a string. Your description mentions two substrings, the statement has just one, measured by occurrences of SUBSTR. The concatenation operator, if supported by your dialect of SQL, will then not see what you intended it to see (two SUBSTRings).
将m在m.name需求已经确定，这就是失踪。CONCAT(MySQL?) 应该使用字符串，1除了字符串之外还传递一个数字 ( )。您的描述提到了两个子字符串，该语句只有一个，通过出现SUBSTR. 连接运算符，如果您的 SQL 方言支持，则不会看到您想要看到的内容（两个SUBSTRing）。
