SQL 提取名字和姓氏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5145791/
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
Extracting First Name and Last Name
提问by Sage
I have a column named Name in a table called test which has Full name and I am trying to extract First name and Last Name. So I wrote query something like this:
我在名为 test 的表中有一个名为 Name 的列，它具有全名，我正在尝试提取名字和姓氏。所以我写了这样的查询：
SELECT
[Name],
LEFT([Name],CHARINDEX(' ',[Name])-1) AS FIRST_NAME,
SUBSTRING([Name],CHARINDEX(' ',[Name])+1,LEN([Name])) AS LAST_NAME
FROM Test
But It is giving me error saying:
但它给了我错误说：
Msg 537, Level 16, State 2, Line 1 Invalid length parameter passed to the LEFT or SUBSTRING function.
消息 537，级别 16，状态 2，第 1 行传递给 LEFT 或 SUBSTRING 函数的长度参数无效。
Thta's because I have some values in the name like:
那是因为我在名称中有一些值，例如：
Name:
姓名：
Hopkins
霍普金斯大学
How do I handle these?
我如何处理这些？
回答by Sage
Declare @t table ( [Name] varchar(100) )
insert into @t ( Name )
VALUES ( 'dennis hopper' ), ('keanu reaves'), ('thatgirl')
SELECT
[Name],
CHARINDEX(' ', [Name]),
CASE WHEN CHARINDEX(' ', [Name]) > 0 THEN
LEFT([Name],CHARINDEX(' ',[Name])-1)
ELSE
[Name]
END as FIRST_NAME,
CASE WHEN CHARINDEX(' ', [Name]) > 0 THEN
SUBSTRING([Name],CHARINDEX(' ',[Name])+1, ( LEN([Name]) - CHARINDEX(' ',[Name])+1) )
ELSE
NULL
END as LAST_NAME
FROM @t
回答by George Mastros
The problem with your original code is here:
您的原始代码的问题在这里：
CHARINDEX(' ',[Name])-1
If [Name] does not contain a space, CharIndex returns 0. You subtract 1 and feed this in to the Left function. When the 2nd parameter to the left function is -1, you will get this error. In my opinion, the easiest way to "fix" this problem is to give the CharIndex function something to find, like this:
如果 [Name] 不包含空格，则 CharIndex 返回 0。您减去 1 并将其输入 Left 函数。当左侧函数的第二个参数为 -1 时，您将收到此错误。在我看来，“修复”这个问题的最简单方法是给 CharIndex 函数一些东西来查找，像这样：
CHARINDEX(' ',[Name] + ' ')-1
Now... this code cannot fail.
现在...此代码不能失败。
You only NEED to do this one place in your original code, but you should add it to the LAST_NAME part also. If you don't, you will get incorrect results (eventhough you will not get an error).
您只需要在原始代码中的一个地方执行此操作，但您也应该将其添加到 LAST_NAME 部分。如果不这样做，您将得到不正确的结果（即使您不会得到错误）。
SELECT [Name],
LEFT([Name],CHARINDEX(' ',[Name] + ' ')-1) AS FIRST_NAME,
SUBSTRING([Name],CHARINDEX(' ',[Name] + ' ')+1,LEN([Name])) AS LAST_NAME
FROM Test
This query will return the same results as the query suggested by @Sage, but (in my opinion) it is easier to read, and easier to understand.
此查询将返回与@Sage 建议的查询相同的结果，但（在我看来）它更易于阅读和理解。
回答by Eve P Engh
we may also use locate function
我们也可以使用定位功能
SELECT Name, LEFT(Name,locate(' ',Name)-1) AS FIRST_NAME, SUBSTRING(Name,locate(' ',Name)+1,LENgth(Name)) AS LAST_NAME FROM test
SELECT Name, LEFT(Name,locate(' ',Name)-1) AS FIRST_NAME, SUBSTRING(Name,locate(' ',Name)+1,LENgth(Name)) AS LAST_NAME FROM test
