在 SQL Server CE 中将一列拆分为两列 [英] Splitting one column into two columns in 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 中将一列拆分为两列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！