如何在 SQL Server 中将一列拆分为两列 [英] How to split one column into two columns in SQL Server

问题描述

我有一个关于 SQL Server 的小问题，请告诉我如何解决这个问题

I have small question about SQL Server, please tell me how to solve this issue

表:emp

   id    name
  ---------------
   1    abc_rao
   2    nani
   3    hari_babu
   4    kalibabu
   5    ab_tan

基于该表，我想要如下输出

Based on that table I want output like below

   id   firstname   lastname
   1      abc       rao
   2      nani      nothing
   3      hari      babu
   4      kalibabu  nothing
   5      ab        tan

我是这样试的:

select 
    SUBSTRING(name, 1, CHARINDEX('_', name) - 1) as firstname , 
    SUBSTRING(name, CHARINDEX('_', name) + 1, LEN(name)) as lastname 
from emp

但我没有得到完全预期的结果.

but I'm not getting exactly the expected result.

相反，我收到了一个错误:

Instead, I'm getting an error:

消息 537，级别 16，状态 2，第 3 行
传递给 LEFT 或 SUBSTRING 函数的长度参数无效.

Msg 537, Level 16, State 2, Line 3
Invalid length parameter passed to the LEFT or SUBSTRING function.

请告诉我如何解决这个问题

Please tell me how to solve this

在 SQL Server 中使用查询的问题

Issue using query in SQL Server

推荐答案

试试这个:

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 命令来控制姓氏是否可用.

you can use CASE command to control is last name available.

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

UPDATED: sqlfiddle added

这篇关于如何在 SQL Server 中将一列拆分为两列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！