oracle 将姓氏与全名分开
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13879851/
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
Separate last name from a full name
提问by Taoqir
I want to display my data in this format:
我想以这种格式显示我的数据：
First_Name Mid_Name Last_Name
All Ahmad Khan
This is my code
这是我的代码
select substr(customer_name1, 0, instr(customer_name1, ' ')-1) as first_name
, substr(customer_name1, instr(customer_name1, ' ')+1) as mid_name
, ... as last_name
from sttm_customers
where customer_name1 = 'ALI AHMAD KHAN'
Can anyone help me with how I should add last_name in my report?
任何人都可以帮助我如何在报告中添加姓氏？
回答by Andy Lester
You can't do it reliably, because you don't have a reliable way to know what is a first name and what is a last name. The examples above show splitting on whitespace, but that doesn't do it reliably.
你不能可靠地做到这一点，因为你没有一种可靠的方法来知道什么是名字，什么是姓氏。上面的示例显示了对空格的拆分，但这并不可靠。
If you have "John Fitzgerald Kennedy", then it's easy. Your first name is "John", middle is "Fitzgerald" and last is "Kennedy". No problem.
如果你有“约翰·菲茨杰拉德·肯尼迪”，那就很容易了。你的名字是“约翰”，中间是“菲茨杰拉德”，最后是“肯尼迪”。没问题。
What if you have "Mary Ann Summers"? First name should be "Mary Ann" and last name is "Summers", but splitting on whitespace doesn't know that. Splitting on whitespace gives you "Mary", "Ann" and "Summers", which is wrong.
如果你有“玛丽·安·萨默斯”怎么办？名字应该是“Mary Ann”，姓氏应该是“Summers”，但是在空格上拆分并不知道这一点。拆分空白会给你“玛丽”、“安”和“夏天”，这是错误的。
In the opposite direction, what if you have "Eddie Van Halen"? "Eddie" is first name, and "Van Halen" is the last name, but breaking on whitespace would give you "Eddie", "Van" and "Halen", which is wrong.
在相反的方向，如果你有“Eddie Van Halen”怎么办？“Eddie”是名字，“Van Halen”是姓氏，但打破空白会给你“Eddie”、“Van”和“Halen”，这是错误的。
For that matter, you could have "Mary Ann Van Halen", and then what do you do?
就此而言，你可以拥有“玛丽安范海伦”，然后你会怎么做？
Bottom line: If you haven't captured your first and last name as separate data points, you can't reliably split them apart.
底线：如果您没有将您的名字和姓氏捕获为单独的数据点，则无法可靠地将它们分开。
回答by DazzaL
SQL> select substr(customer_name1, 0, instr(customer_name1, ' ')-1) as first_name
2 , substr(customer_name1, instr(customer_name1, ' ')+1,
3 instr(customer_name1, ' ', -1, 1) - instr(customer_name1, ' ') - 1) as mid_name
4 , substr(customer_name1, instr(customer_name1, ' ', -1) + 1) last_name
5 from (select 'ALI AHMAD KHAN' customer_name1 from dual)
6 /
FIR MID_N LAST
--- ----- ----
ALI AHMAD KHAN
this copes with the name having multiple middle names.
这可以处理具有多个中间名的名称。
回答by Taryn
This is a bit ugly, but it will separate each piece:
这有点难看，但它会将每一部分分开：
with data(customer_name1) as
(
select 'ALI AHMAD KHAN'
from dual
),
first(firstname, customer_name1) as
(
select substr(customer_name1, 0, instr(customer_name1, ' ')-1) as first_name,
ltrim(replace(customer_name1, substr(customer_name1, 0, instr(customer_name1, ' ')-1), '')) customer_name1
from data
),
middle(firstname, middlename, customer_name1) as
(
select firstname,
substr(customer_name1, 0, instr(customer_name1, ' ')-1) middlename,
ltrim(replace(customer_name1, substr(customer_name1, 0, instr(customer_name1, ' ')-1), '')) customer_name1
from first
)
select firstname, middlename, customer_name1 as LastName
from middle
回答by nayef harb
Put -1 as the starting postions and the sub string will start counting from the end and then
将 -1 作为起始位置，子字符串将从末尾开始计数，然后
find the instr for the white space hope it helps
找到空白的指令希望它有帮助
回答by sudharsonn s
To seperate the firstname, middlename, lastname from full name.
将名字、中间名、姓氏与全名分开。
It worked oracle 11g. Consider table username with one field call unamewhich contains following names for example.
它奏效了oracle 11g。例如，考虑带有一个字段 call uname 的表 username ，其中包含以下名称。
sachin ramesh tendulkar
shiva sudharson s
shiva kumar s
shiva nandhan s
shiva selvan s
To split the name as firstname, middlename, lastname below query works.
将名称拆分为名字，中间名，姓氏在查询下方有效。
select
substr(uname,1, instr(uname,' ')) as firstname,
substr(uname, instr(uname, ' '), instr(uname, ' ', 1, 2)-instr(uname, ' ')) as middlename,
substr(uname, instr(uname, ' ',1,2), length(uname)) as lastname from username;
Result will be like:
结果将是这样的：
sachin ramesh tendulkar
shiva sudharson s
shiva kumar s
shiva nandhan s
shiva selvan s
