时间戳

首页 > 解决方案 > 拆分字符串值并在 INSERT 和 UPDATE 命令中使用

sql - 拆分字符串值并在 INSERT 和 UPDATE 命令中使用

问题描述

我为我们的厨房实施了一个小型 ERP 系统。该系统用于跟踪我们的杂货，因此它包含商品（称为食品）及其名称和 UPC 代码。随着时间的推移，名称和 upcs 开始重叠并产生数据问题。在第一次尝试中，我们开始将制造商添加到食品的名称中，从而为我们提供如下所示的数据。

ID 食品名
1 嗜酸乳杆菌；Joghurt
2 Aibler;Rapsöl
3 Allos;Choco Confiserie
4 阿尔纳图拉；丁克尔巴特克斯
5 阿尔纳图拉；萨拉特肯混合

名字是德国人，对不起。名称的第一部分是制造商，第二部分是食品的名称。我现在介绍了一个名为“制造商”的新表，其中包含一个 ID 和一个名称。食物的桌子被ManufacturerId田地延长了。我想要以下表格

制造商

ID 姓名
1 嗜酸乳杆菌
2 艾布勒
3 阿洛斯
4 阿尔纳图拉
5 阿尔纳图拉

食物

ID 食品名 制造商 ID
1 焦尔特 1
2 拉普索尔 2
3 巧克力糖果店 3
4 丁克尔巴特克斯 4
5 萨拉特肯混合 5

我试着绕着一条 SQL 语句扭曲我的脑袋，这将为我解决这个问题，但我找不到任何解决方案。

要求

   select Name from Foods
   where Foods.Name like '%;%'
   order by [Name]

我尝试使用函数 STRING_SPLIT、LEFT()、RIGHT() 构建，但无法解决。根据文档字符串拆分也不保证结果始终处于相同的顺序。我希望有人对我有想法。

谢谢

标签： sqlsql-server

解决方案

这是非常可行的，但是像这样的元数据更改通常需要多次操作数据转换才能完成。

首先，这是我对您当前状态的理解：

/*  Current state of things: */
CREATE TABLE Food
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    FoodName NVARCHAR(255)
)
GO
INSERT INTO Food (Foodname) VALUES
    (N'Acidophilus;Joghurt'),
    (N'Aibler;Rapsöl'),
    (N'Allos;Choco Confiserie'),
    (N'Alnatura; Dinkel Butterkeks'),
    (N'Alnatura; Salatkerne Mix');

假设这是正确的，那么这里是 DDL 更改：

/* New Manufacturer Table */
CREATE TABLE Manufacturer
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(255)
)
GO
/* Extend Food table with new column */
ALTER TABLE Food ADD ManufacturerID INT
GO

更改元数据/DDL 后，您可以进行数据转换。您的案例很简单，可以通过两条 SQL 语句完成：

/* Data Transforms */
-- Get the Manufacturer Names:
WITH 
  cteFoodOff AS (SELECT *, CHARINDEX(';', FoodName) AS offset FROM Food)
, cteFoodMan AS 
  (
    SELECT  *, CASE WHEN offset > 0 THEN LEFT(FoodName, offset-1) ELSE '' END AS MName
    FROM    cteFoodOff
    WHERE   offset > 0
  )
INSERT INTO Manufacturer
SELECT  DISTINCT MName
FROM    cteFoodMan
WHERE   MName NOT IN(Select Name From Manufacturer)
;

-- Add ManufacturerIDs into Food table and remove Manufacturer Name from FoodName
WITH 
  cteFoodOff AS (SELECT *, CHARINDEX(';', FoodName) AS offset FROM Food)
, cteFoodMan AS 
  (
    SELECT  *, CASE WHEN offset > 0 THEN LEFT(FoodName, offset-1) ELSE '' END AS MName
    FROM    cteFoodOff
    WHERE   offset > 0
  )
UPDATE cteFoodMan
SET ManufacturerID  = (Select ID From Manufacturer Where Name = MName),
    FoodName = LTRIM(RIGHT(FoodName, LEN(FoodName) - offset))
;
GO

如果您需要在有任何实时用户/客户端时执行此操作（不建议更改元数据，因为客户端访问也需要进行代码切换），那么您可能需要将其包装在事务中，并且您可以另外需要一个视图来隐藏旧客户端代码的更改。

推荐阅读