请尝试以下操作：

select 
    case when CHARINDEX('_',name)>0 
         then SUBSTRING(name,1,CHARINDEX('_',name)-1) 
         else name end firstname, 
    CASE WHEN CHARINDEX('_',name)>0 
         THEN SUBSTRING(name,CHARINDEX('_',name)+1,len(name))  
         ELSE NULL END as lastname
from emp

您可以使用CASE命令来控制姓氏是否可用。
SQL小提琴

MS SQL Server 2008架构安装程序：
查询1：

declare @t table (id int, name  varchar(50))

insert into @t (id,name) values( 1    ,'abc_rao')
insert into @t (id,name) values( 2    ,'nani')
insert into @t (id,name) values( 3    ,'hari_babu')
insert into @t (id,name) values( 4    ,'kalibabu')
insert into @t (id,name) values( 5    ,'ab_tan')

select 
    case when CHARINDEX('_',name)>0 
         then SUBSTRING(name,1,CHARINDEX('_',name)-1) 
         else name end firstname, 
    CASE WHEN CHARINDEX('_',name)>0 
         THEN SUBSTRING(name,CHARINDEX('_',name)+1,len(name))  
         ELSE NULL END as lastname
from @t

结果：

| FIRSTNAME | LASTNAME |
|-----------|----------|
|       abc |      rao |
|      nani |   (null) |
|      hari |     babu |
|  kalibabu |   (null) |
|        ab |      tan |

已更新：添加了sqlfiddle

declare @table table(name varchar(300))
insert into @table values('Raj Parmar')
insert into @table values('Ronak mer')
insert into @table values('Mayuri nikunj trivedi')

Select 
DISTINCT name,
   SUBSTRING(name, 1, CHARINDEX(' ', name) - 1) as FirstName,
   RTRIM(LTRIM(REPLACE(REPLACE(name,SUBSTRING(name , 1, CHARINDEX(' ', name) - 1),''),REVERSE( LEFT( REVERSE(name), CHARINDEX(' ', REVERSE(name))-1 ) ),'')))as MiddleName,
   REVERSE( LEFT( REVERSE(name), CHARINDEX(' ', REVERSE(name))-1 ) ) as LastName
From @table

name                    FirstName      MiddleName          LastName
Mayuri nikunj trivedi    Mayuri        nikunj               trivedi
Raj Parmar               Raj                                Parmar
Ronak mer               Ronak                               mer

你错过了字符串中没有下划线的情况，这会导致错误。

select
    case CHARINDEX('_', name) when 0 then name
        else SUBSTRING(name, 1, CHARINDEX('_', name) - 1) end firstName,

    case CHARINDEX('_', name) when 0 then name
        else SUBSTRING(name, CHARINDEX('_', name) + 1, LEN(name)) end lastname 
from emp

我假设在一个单词的情况下，您希望将它们同时显示为名字和姓氏。您可以将该值更改为任何您喜欢的值。

declare @tbl table(name varchar(20))

insert into @tbl values ('Abdul$Rahim')

insert into @tbl values('Tariq$Jameel')

select

PARSENAME(replace(name,'$','.'),2) as firstname,
PARSENAME(replace(name,'$','.'),1) as lastname

from @tbl

使用Split_string函数和动态透视表更容易
将@ TestPerformance声明为表（DirtyId整数标识，[文本] varchar（最大值））
插入到@测试性能（[文本]）值（“佩德罗·格瓦拉，58岁，牙买加”）插入到@测试性能（[文本]）值（“胡安·桑多瓦尔，80岁，大肠杆菌”）插入到@测试性能（[文本]）值（“卡拉·希诺霍萨，71岁，牙买加”）插入到@测试性能（[文本]）值（“路易斯·冈萨雷斯，15岁，牙买加”）
select [2] AS [Id]，[1] AS [Name]，[3] AS [State] from（select TP.DirtyId，ROW_NUMBER（）OVER（ORDER BY TP.DirtyId）-（（TP.DirtyId -1）* 3）AS行_Csc_Id，txt.值from @TestPorformance TP交叉应用字符串拆分（tp.[Text]，'，'）AS Txt）X透视（（[1]，[2]，[3]）中行_Csc_Id的最小值（[value]）））p