提取名字和姓氏

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?

我该如何处理?

3 个解决方案

#1

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

#2

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建议的查询相同的结果,但(在我看来)它更容易阅读,更容易理解。

#3

-4

we may also use locate function

我们也可以使用locate函数

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(名称,locate('',Name)+ 1,LENgth(Name))AS LAST_NAME FROM测试

#1

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

#2