I have a column which has FirstName and LastName together. I'm writing a report to separate the FirstName And LastName. How do I get the FirstName and LastName separated in T-SQL?
我有一个列,它有名和姓。我正在写一份报告,把名字和姓分开。如何在T-SQL中分离FirstName和LastName ?
10 个解决方案
#1
44
Assuming the FirstName
is all of the characters up to the first space:
假设第一个名字是第一个空格前的所有字符:
SELECT
SUBSTRING(username, 1, CHARINDEX(' ', username) - 1) AS FirstName,
SUBSTRING(username, CHARINDEX(' ', username) + 1, 8000) AS LastName
FROM
whereever
#2
15
The easiest way I can find to do it is:
我能找到的最简单的方法是:
SELECT
SUBSTRING(FullName, 1, CHARINDEX(' ', FullName) - 1) AS FirstName,
REVERSE(SUBSTRING(REVERSE(FullName), 1, CHARINDEX(' ', REVERSE(FullName)) - 1)) AS LastName
FROM
[PERSON_TABLE]
#3
12
This should work:
这应该工作:
Select
LTRIM(RTRIM(SUBSTRING(FullName, 0, CHARINDEX(' ', FullName)))) As FirstName
, LTRIM(RTRIM(SUBSTRING(FullName, CHARINDEX(' ', FullName)+1, 8000)))As LastName
FROM TABLE
Edit: Adopted Aaron's and Jonny's hint with the fixed length of 8000 to avoid unnecessary calculations.
编辑:采用Aaron和Jonny的提示,固定长度为8000,避免不必要的计算。
#4
6
validate last name is blank
SELECT
person.fullName,
(CASE WHEN 0 = CHARINDEX(' ', person.fullName)
then person.fullName
ELSE SUBSTRING(person.fullName, 1, CHARINDEX(' ', person.fullName)) end) as first_name,
(CASE WHEN 0 = CHARINDEX(' ', person.fullName)
THEN ''
ELSE SUBSTRING(person.fullName,CHARINDEX(' ', person.fullName), LEN(person.fullName) )end) last_name
FROM person
#5
5
This will take care of names like "Firstname Z. Lastname" and "First Z Last"
这将处理“Firstname Z. Lastname”和“First Z Last”等名称
SELECT
CASE
WHEN CHARINDEX(' ',name) = 0 THEN name
WHEN CHARINDEX(' ',name) = PATINDEX('% _[., ]%',name) THEN RTRIM(SUBSTRING(name, 1, CHARINDEX(' ',name) + 2))
ELSE SUBSTRING(name,1, CHARINDEX(' ',name))
END [firstname]
,CASE
WHEN CHARINDEX(' ',name) = 0 THEN ''
WHEN CHARINDEX(' ',name) = PATINDEX('% _[., ]%',name) THEN LTRIM(SUBSTRING(name, CHARINDEX(' ',name) + 3,1000))
ELSE SUBSTRING(name,CHARINDEX(' ',name)+1,1000)
END [lastname]
FROM [myTable]
#6
3
Here is a more elaborated solution with a SQL function:
下面是一个更复杂的SQL函数解决方案:
GetFirstname
GetFirstname
CREATE FUNCTION [dbo].[ufn_GetFirstName]
(
@FullName varchar(500)
)
RETURNS varchar(500)
AS
BEGIN
-- Declare the return variable here
DECLARE @RetName varchar(500)
SET @FullName = replace( replace( replace( replace( @FullName, '.', '' ), 'Mrs', '' ), 'Ms', '' ), 'Mr', '' )
SELECT
@RetName =
CASE WHEN charindex( ' ', ltrim( rtrim( @FullName ) ) ) > 0 THEN left( ltrim( rtrim( @FullName ) ), charindex( ' ', ltrim( rtrim( @FullName ) ) ) - 1 ) ELSE '' END
RETURN @RetName
END
GetLastName
GetLastName
CREATE FUNCTION [dbo].[ufn_GetLastName]
(
@FullName varchar(500)
)
RETURNS varchar(500)
AS
BEGIN
DECLARE @RetName varchar(500)
IF(right(ltrim(rtrim(@FullName)), 2) <> ' I')
BEGIN
set @RetName = left(
CASE WHEN
charindex( ' ', reverse( ltrim( rtrim(
replace( replace( replace( replace( replace( replace( @FullName, ' Jr', '' ), ' III', '' ), ' II', '' ), ' Jr.', '' ), ' Sr', ''), 'Sr.', '')
) ) ) ) > 0
THEN
right( ltrim( rtrim(
replace( replace( replace( replace( replace( replace( @FullName, ' Jr', '' ), ' III', '' ), ' II', '' ), ' Jr.', '' ), ' Sr', ''), 'Sr.', '')
) ) , charindex( ' ', reverse( ltrim( rtrim(
replace( replace( replace( replace( replace( replace( @FullName, ' Jr', '' ), ' III', '' ), ' II', '' ), ' Jr.', '' ), ' Sr', ''), 'Sr.', '')
) ) ) ) - 1 )
ELSE '' END
, 25 )
END
ELSE
BEGIN
SET @RetName = left(
CASE WHEN
charindex( ' ', reverse( ltrim( rtrim(
replace( replace( replace( replace( replace( replace( replace( @FullName, ' Jr', '' ), ' III', '' ), ' II', '' ), ' I', '' ), ' Jr.', '' ), ' Sr', ''), 'Sr.', '')
) ) ) ) > 0
THEN
right( ltrim( rtrim(
replace( replace( replace( replace( replace( replace( replace( @FullName, ' Jr', '' ), ' III', '' ), ' II', '' ), ' I', '' ), ' Jr.', '' ), ' Sr', ''), 'Sr.', '')
) ) , charindex( ' ', reverse( ltrim( rtrim(
replace( replace( replace( replace( replace( replace( replace( @FullName, ' Jr', '' ), ' III', '' ), ' II', '' ), ' I', '' ), ' Jr.', '' ), ' Sr', ''), 'Sr.', '')
) ) ) ) - 1 )
ELSE '' END
, 25 )
END
RETURN @RetName
END
USE:
使用:
SELECT dbo.ufn_GetFirstName(Fullname) as FirstName, dbo.ufn_GetLastName(Fullname) as LastName FROM #Names
#7
2
You may have problems if the Fullname doesn't contain a space. Assuming the whole of FullName goes to Surname if there is no space and FirstName becomes an empty string, then you can use this:
如果全名不包含空格,您可能会有问题。假设如果没有空格,全名变成了姓,而FirstName变成了空字符串,那么您可以使用以下方法:
SELECT
RTRIM(LEFT(FullName, CHARINDEX(' ', FullName))) AS FirstName,
SUBSTRING(FullName, CHARINDEX(' ', FullName) + 1, 8000) AS LastName
FROM
MyNameTable;
#8
2
I think below query will be helpful to split FirstName and LastName from FullName even if there is only FirstName. For example: 'Philip John' can be split into Philip and John. But if there is only Philip, because of the charIndex of Space is 0, it will only give you ''.
我认为下面的查询将有助于从FullName中拆分FirstName和LastName,即使只有FirstName。例如:“Philip John”可以分为Philip和John。但是如果只有Philip,因为空间的charIndex是0,它只会给你"
Try the below one.
下面的一个尝试。
declare @FullName varchar(100)='Philp John'
Select
LTRIM(RTRIM(SUBSTRING(@FullName, 0, CHARINDEX(' ', @FullName+' ')))) As FirstName
, LTRIM(RTRIM(SUBSTRING(@FullName, CHARINDEX(' ', @FullName+' ')+1, 8000)))As LastName
Hope this will help you. :)
希望这能对你有所帮助。:)
#9
2
You could do this if firstname and surname are separated by space:
如果名字和姓氏用空格分开,你可以这样做:
SELECT SUBSTRING(FirstAndSurnameCol, 0, CHARINDEX(' ', FirstAndSurnameCol)) Firstname,
SUBSTRING(FirstAndSurnameCol, CHARINDEX(' ', FirstAndSurnameCol)-1, LEN(FirstAndSurnameCol)) Surname FROM ...
#10
1
The code below works with Last, First M name strings. Substitute "Name" with your name string column name. Since you have a period as a final character when there is a middle initial, you would replace the 2's with 3's in each of the lines (2, 6, and 8)- and change "RIGHT(Name, 1)" to "RIGHT(Name, 2)" in line 8.
下面的代码使用最后的,第M个名称字符串。用您的名字字符串列名替换“Name”。因为在中间的初始阶段,你有一个作为最终字符的周期,你将在每一行(2、6和8)中替换2的3,并且在第8行中将“RIGHT(Name, 1)”改为“RIGHT(Name, 2)”。
SELECT SUBSTRING(Name, 1, CHARINDEX(',', Name) - 1) LastName ,
CASE WHEN LEFT(RIGHT(Name, 2), 1) <> ' '
THEN LTRIM(SUBSTRING(Name, CHARINDEX(',', Name) + 1, 99))
ELSE LEFT(LTRIM(SUBSTRING(Name, CHARINDEX(',', Name) + 1, 99)),
LEN(LTRIM(SUBSTRING(Name, CHARINDEX(',', Name) + 1, 99)))
- 2)
END FirstName ,
CASE WHEN LEFT(RIGHT(Name, 2), 1) = ' ' THEN RIGHT(Name, 1)
ELSE NULL
END MiddleName
#1
44
Assuming the FirstName
is all of the characters up to the first space:
假设第一个名字是第一个空格前的所有字符:
SELECT
SUBSTRING(username, 1, CHARINDEX(' ', username) - 1) AS FirstName,
SUBSTRING(username, CHARINDEX(' ', username) + 1, 8000) AS LastName
FROM
whereever
#2
15
The easiest way I can find to do it is:
我能找到的最简单的方法是:
SELECT
SUBSTRING(FullName, 1, CHARINDEX(' ', FullName) - 1) AS FirstName,
REVERSE(SUBSTRING(REVERSE(FullName), 1, CHARINDEX(' ', REVERSE(FullName)) - 1)) AS LastName
FROM
[PERSON_TABLE]
#3
12
This should work:
这应该工作:
Select
LTRIM(RTRIM(SUBSTRING(FullName, 0, CHARINDEX(' ', FullName)))) As FirstName
, LTRIM(RTRIM(SUBSTRING(FullName, CHARINDEX(' ', FullName)+1, 8000)))As LastName
FROM TABLE
Edit: Adopted Aaron's and Jonny's hint with the fixed length of 8000 to avoid unnecessary calculations.
编辑:采用Aaron和Jonny的提示,固定长度为8000,避免不必要的计算。
#4
6
validate last name is blank
SELECT
person.fullName,
(CASE WHEN 0 = CHARINDEX(' ', person.fullName)
then person.fullName
ELSE SUBSTRING(person.fullName, 1, CHARINDEX(' ', person.fullName)) end) as first_name,
(CASE WHEN 0 = CHARINDEX(' ', person.fullName)
THEN ''
ELSE SUBSTRING(person.fullName,CHARINDEX(' ', person.fullName), LEN(person.fullName) )end) last_name
FROM person
#5
5
This will take care of names like "Firstname Z. Lastname" and "First Z Last"
这将处理“Firstname Z. Lastname”和“First Z Last”等名称
SELECT
CASE
WHEN CHARINDEX(' ',name) = 0 THEN name
WHEN CHARINDEX(' ',name) = PATINDEX('% _[., ]%',name) THEN RTRIM(SUBSTRING(name, 1, CHARINDEX(' ',name) + 2))
ELSE SUBSTRING(name,1, CHARINDEX(' ',name))
END [firstname]
,CASE
WHEN CHARINDEX(' ',name) = 0 THEN ''
WHEN CHARINDEX(' ',name) = PATINDEX('% _[., ]%',name) THEN LTRIM(SUBSTRING(name, CHARINDEX(' ',name) + 3,1000))
ELSE SUBSTRING(name,CHARINDEX(' ',name)+1,1000)
END [lastname]
FROM [myTable]
#6
3
Here is a more elaborated solution with a SQL function:
下面是一个更复杂的SQL函数解决方案:
GetFirstname
GetFirstname
CREATE FUNCTION [dbo].[ufn_GetFirstName]
(
@FullName varchar(500)
)
RETURNS varchar(500)
AS
BEGIN
-- Declare the return variable here
DECLARE @RetName varchar(500)
SET @FullName = replace( replace( replace( replace( @FullName, '.', '' ), 'Mrs', '' ), 'Ms', '' ), 'Mr', '' )
SELECT
@RetName =
CASE WHEN charindex( ' ', ltrim( rtrim( @FullName ) ) ) > 0 THEN left( ltrim( rtrim( @FullName ) ), charindex( ' ', ltrim( rtrim( @FullName ) ) ) - 1 ) ELSE '' END
RETURN @RetName
END
GetLastName
GetLastName
CREATE FUNCTION [dbo].[ufn_GetLastName]
(
@FullName varchar(500)
)
RETURNS varchar(500)
AS
BEGIN
DECLARE @RetName varchar(500)
IF(right(ltrim(rtrim(@FullName)), 2) <> ' I')
BEGIN
set @RetName = left(
CASE WHEN
charindex( ' ', reverse( ltrim( rtrim(
replace( replace( replace( replace( replace( replace( @FullName, ' Jr', '' ), ' III', '' ), ' II', '' ), ' Jr.', '' ), ' Sr', ''), 'Sr.', '')
) ) ) ) > 0
THEN
right( ltrim( rtrim(
replace( replace( replace( replace( replace( replace( @FullName, ' Jr', '' ), ' III', '' ), ' II', '' ), ' Jr.', '' ), ' Sr', ''), 'Sr.', '')
) ) , charindex( ' ', reverse( ltrim( rtrim(
replace( replace( replace( replace( replace( replace( @FullName, ' Jr', '' ), ' III', '' ), ' II', '' ), ' Jr.', '' ), ' Sr', ''), 'Sr.', '')
) ) ) ) - 1 )
ELSE '' END
, 25 )
END
ELSE
BEGIN
SET @RetName = left(
CASE WHEN
charindex( ' ', reverse( ltrim( rtrim(
replace( replace( replace( replace( replace( replace( replace( @FullName, ' Jr', '' ), ' III', '' ), ' II', '' ), ' I', '' ), ' Jr.', '' ), ' Sr', ''), 'Sr.', '')
) ) ) ) > 0
THEN
right( ltrim( rtrim(
replace( replace( replace( replace( replace( replace( replace( @FullName, ' Jr', '' ), ' III', '' ), ' II', '' ), ' I', '' ), ' Jr.', '' ), ' Sr', ''), 'Sr.', '')
) ) , charindex( ' ', reverse( ltrim( rtrim(
replace( replace( replace( replace( replace( replace( replace( @FullName, ' Jr', '' ), ' III', '' ), ' II', '' ), ' I', '' ), ' Jr.', '' ), ' Sr', ''), 'Sr.', '')
) ) ) ) - 1 )
ELSE '' END
, 25 )
END
RETURN @RetName
END
USE:
使用:
SELECT dbo.ufn_GetFirstName(Fullname) as FirstName, dbo.ufn_GetLastName(Fullname) as LastName FROM #Names
#7
2
You may have problems if the Fullname doesn't contain a space. Assuming the whole of FullName goes to Surname if there is no space and FirstName becomes an empty string, then you can use this:
如果全名不包含空格,您可能会有问题。假设如果没有空格,全名变成了姓,而FirstName变成了空字符串,那么您可以使用以下方法:
SELECT
RTRIM(LEFT(FullName, CHARINDEX(' ', FullName))) AS FirstName,
SUBSTRING(FullName, CHARINDEX(' ', FullName) + 1, 8000) AS LastName
FROM
MyNameTable;
#8
2
I think below query will be helpful to split FirstName and LastName from FullName even if there is only FirstName. For example: 'Philip John' can be split into Philip and John. But if there is only Philip, because of the charIndex of Space is 0, it will only give you ''.
我认为下面的查询将有助于从FullName中拆分FirstName和LastName,即使只有FirstName。例如:“Philip John”可以分为Philip和John。但是如果只有Philip,因为空间的charIndex是0,它只会给你"
Try the below one.
下面的一个尝试。
declare @FullName varchar(100)='Philp John'
Select
LTRIM(RTRIM(SUBSTRING(@FullName, 0, CHARINDEX(' ', @FullName+' ')))) As FirstName
, LTRIM(RTRIM(SUBSTRING(@FullName, CHARINDEX(' ', @FullName+' ')+1, 8000)))As LastName
Hope this will help you. :)
希望这能对你有所帮助。:)
#9
2
You could do this if firstname and surname are separated by space:
如果名字和姓氏用空格分开,你可以这样做:
SELECT SUBSTRING(FirstAndSurnameCol, 0, CHARINDEX(' ', FirstAndSurnameCol)) Firstname,
SUBSTRING(FirstAndSurnameCol, CHARINDEX(' ', FirstAndSurnameCol)-1, LEN(FirstAndSurnameCol)) Surname FROM ...
#10
1
The code below works with Last, First M name strings. Substitute "Name" with your name string column name. Since you have a period as a final character when there is a middle initial, you would replace the 2's with 3's in each of the lines (2, 6, and 8)- and change "RIGHT(Name, 1)" to "RIGHT(Name, 2)" in line 8.
下面的代码使用最后的,第M个名称字符串。用您的名字字符串列名替换“Name”。因为在中间的初始阶段,你有一个作为最终字符的周期,你将在每一行(2、6和8)中替换2的3,并且在第8行中将“RIGHT(Name, 1)”改为“RIGHT(Name, 2)”。
SELECT SUBSTRING(Name, 1, CHARINDEX(',', Name) - 1) LastName ,
CASE WHEN LEFT(RIGHT(Name, 2), 1) <> ' '
THEN LTRIM(SUBSTRING(Name, CHARINDEX(',', Name) + 1, 99))
ELSE LEFT(LTRIM(SUBSTRING(Name, CHARINDEX(',', Name) + 1, 99)),
LEN(LTRIM(SUBSTRING(Name, CHARINDEX(',', Name) + 1, 99)))
- 2)
END FirstName ,
CASE WHEN LEFT(RIGHT(Name, 2), 1) = ' ' THEN RIGHT(Name, 1)
ELSE NULL
END MiddleName