在 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:

第一列

Ned Stark
Tyrion Lannister
Daenerys Targaryen
Robert Baratheon

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

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

名字姓氏

Names Surnames

Ned               Stark
Tyrion            Lannister
Daenerys          Targaryen
Robert            Baratheon

由于我使用的是 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?

答

这应该可行.

SELECT SUBSTRING([Name], 0, CHARINDEX(' ', [Name], 0)) [FirstName],
       SUBSTRING([Name], CHARINDEX(' ', [Name], 0) + 1, LEN([Name])) [LastName]

SELECT SUBSTRING([Name], 0, CHARINDEX(' ', [Name], 0)) [FirstName],
       SUBSTRING([Name], CHARINDEX(' ', [Name], 0) + 1, LEN([Name])) [LastName] 
FROM
(
    SELECT 'Ned Stark' [Name] UNION 
    SELECT 'Tyrion Lannister' [Name] UNION
    SELECT 'Daenerys Targaryen' [Name] UNION
    SELECT 'Robert Baratheon' [Name]
) n

只需将 from 后面的部分替换为您的表，并将 [Name] 列替换为您的列.

Just replace the part after from with you table and the column [Name] with you column.

在 SQL Server CE 中将一列拆分为两列

相关推荐