c# - 反转 SQL Server 中列的内容

Question

我最近将 access 数据库数据导入到客户的 SQL Server 数据库中。对于其中一个名为“案例”的表格；我将“Case_Name”列设置为“Surname Firstname”，其中“Surname”和“Firstname”是来自 Access 数据库的列。现在，客户希望“Case_Name”列改为“​​Firstname Surname”。所以我的问题是这可以在 T-SQL 中很好地或简单地完成，还是我需要再次重新运行我的 c# 迁移程序或创建一些其他 c# 程序

当前格式:

案例表:

Id         Case_Name         Case_Description
1          Bloggs Joe        Will Management
2          York Susan        Divorce CAse

希望表格看起来像这样:

Id         Case_Name         Case_Description
1          Joe Bloggs        Will Management
2          Susan York        Divorce CAse

Answer 1

变体#1:

DECLARE @temp TABLE
(
      ID INT IDENTITY(1,1)
    , Case_Name NVARCHAR(50)
    , Case_Description NVARCHAR(50)
)

INSERT INTO @temp (Case_Name, Case_Description)
VALUES 
    ('Bloggs Joe', 'Will Management'),
    ('York Susan', 'Divorce CAse')

UPDATE @temp
SET Case_Name = SUBSTRING(Case_Name, CHARINDEX(' ', Case_Name), LEN(Case_Name)) + ' ' + SUBSTRING(Case_Name, 1, CHARINDEX(' ', Case_Name)) 

SELECT * FROM @temp

变体#2:

DECLARE @temp TABLE
(
      ID INT IDENTITY(1,1)
    , Case_Name NVARCHAR(50)
    , Case_Description NVARCHAR(50)
)

INSERT INTO @temp (Case_Name, Case_Description)
VALUES 
    ('Bloggs Joe', 'Will Management'),
    ('York Susan', 'Divorce CAse')

CREATE TABLE dbo.temp
(
      ID INT IDENTITY(1,1) PRIMARY KEY
    , FirstName NVARCHAR(20) NOT NULL
    , LastName NVARCHAR(20) NOT NULL
    , Case_Name AS FirstName + ' ' + LastName
    , Case_Description NVARCHAR(50)
)

INSERT INTO dbo.temp (FirstName, LastName, Case_Description)
SELECT 
      SUBSTRING(Case_Name, CHARINDEX(' ', Case_Name), LEN(Case_Name)) 
    , SUBSTRING(Case_Name, 1, CHARINDEX(' ', Case_Name)) 
    , Case_Description 
FROM @temp

SELECT * FROM dbo.temp

输出:

ID          FirstName            LastName             Case_Name                                 Case_Description
----------- -------------------- -------------------- ----------------------------------------- --------------------------------------------------
1            Joe                 Bloggs                Joe Bloggs                               Will Management
2            Susan               York                  Susan York                               Divorce CAse