SQL中将一列分成两列 [英] Dividing one column into two columns in SQL

问题描述

可能的重复:
在 SQL Server CE 中将一列拆分为两列

我正在用 VS2010 使用 C# 做一个项目.

I am doing a project with VS2010 using C#.

我有一个本地数据库(.sdf 文件).这是我的数据库的示例内容:

I have a local database (.sdf file). Here is the example content of my database:

第一列

Frodo Baggins
Samwise Gamgee
Peregrin Took
Meriadoc Brandybuck
.
.
.

我想要做的是将姓名和姓氏分成两个不同的列，如下所示:

What I am trying to do is split names and surnames into two different columns, like this:

名字                姓氏

Names                         Surnames

Frodo             Baggins
Samwise           Gamgee
Peregrin          Took
Meriadoc          Brandybuck
.                 .
.                 .
.                 .

由于我使用的是 SQL Server CE 3.5 版，LEFT、MID 函数对我不起作用.那么，我该怎么做呢?

Since I'm using SQL Server CE 3.5 Edition, LEFT, MID functions didn't work for me. So, how would I do that?

推荐答案

试试这个:

DECLARE @YourTable table (Column1  varchar(50))
INSERT @YourTable VALUES ('Frodo Baggins')
INSERT @YourTable VALUES ('Samwise Gamgee')
INSERT @YourTable VALUES ('Peregrin Took')
INSERT @YourTable VALUES ('Meriadoc Brandybuck')
INSERT @YourTable VALUES ('aa')
INSERT @YourTable VALUES ('aa bb cc')

SELECT
    LEFT(Column1,CHARINDEX(' ',Column1)) AS Names
    ,RIGHT(Column1,LEN(Column1)-CHARINDEX(' ',Column1)) AS Surnames
    FROM @YourTable

--both queries produce same output
SELECT 
    SUBSTRING(Column1, 0, CHARINDEX(' ', Column1))
    ,SUBSTRING(Column1, CHARINDEX(' ',Column1) + 1, LEN(Column1)) 
FROM @YourTable

输出:

Names       Surnames
----------- -------------
Frodo       Baggins
Samwise     Gamgee
Peregrin    Took
Meriadoc    Brandybuck
            aa
aa          bb cc

(6 row(s) affected)

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