SQL替换字符串中的2-3字母值。

Question

我有一张桌子，上面有联系人和他们的关键技能，价值观被分号隔开：

​

​

我想不出如何在不损害现有的较长技能的情况下取代短小的关键技能。例如，UI包含在word Building中。

对于超过4个字母，我使用下面的SQL脚本来替换value1 (@current)以value2 (@替换)。

DECLARE @current varchar(50) = 'UI'
DECLARE @replace varchar(50) = 'New Skill'
UPDATE database.dbo.contact
SET key_skill = CASE
    WHEN key_skill LIKE '%'+@replace+'%'+@current THEN REPLACE(key_skill, ';'+@current, '')
    WHEN key_skill LIKE '%'+@current+'%'+@replace THEN REPLACE(key_skill, @current+';', '')
    WHEN (key_skill LIKE '%'+@replace+'%'+@current+'%') OR (key_skill LIKE '%'+@current+'%'+@replace+'%') THEN REPLACE(key_skill, @current+';', '')
    WHEN key_skill LIKE '%'+@current+'%' THEN REPLACE(key_skill, @current, @replace)
    ELSE key_skill END
FROM database.dbo.contact
WHERE (key_skill LIKE '%'+@current+'%')

Answer 1

如果有可能的话，你应该尽快改变你的设计。在数据库中将列表作为分隔字符串存储几乎从来没有什么好的理由。数据库已经有了存储列表的完美结构，它们被称为表。将联系人与技能联系起来的第二个表格在这里非常有用。就像这样：

CREATE TABLE dbo.Contact
(
    ContactID INT IDENTITY (1, 1) NOT NULL,
    Name VARCHAR(255) NOT NULL,
    CONSTRAINT PK_Contact__ContractID PRIMARY KEY (ContactID)
);
CREATE TABLE dbo.KeySkill
(
    KeySkillID INT IDENTITY (1, 1) NOT NULL,
    Name VARCHAR(50) NOT NULL,
    CONSTRAINT PK_KeySkill__KeySkillID PRIMARY KEY (KeySkillID)
);
CREATE TABLE dbo.ContactKeySkill
(
    ContactID INT NOT NULL,
    KeySkillID INT NOT NULL,
    CONSTRAINT PK_ConactKeySkill__ContactID_KeySkillID PRIMARY KEY (ContactID, KeySkillID),
    CONSTRAINT FK_ContactKeySill__ContactID FOREIGN KEY (ContactID) REFERENCES dbo.Contact (ContactID),
    CONSTRAINT FK_ContactKeySill__KeySkillID FOREIGN KEY (KeySkillID) REFERENCES dbo.KeySkill (KeySkillID)
);

有了这个结构，其他一切都变得非常容易。如果需要，可以按以下方式重新创建现有格式：

SELECT  c.ContactID, c.Name, Skills = STRING_AGG(ks.Name, ';')
FROM    dbo.Contact AS c
        INNER JOIN dbo.ContactKeySkill AS cks
            ON cks.ContactID = c.ContactID
        INNER JOIN dbo.KeySkill AS ks
            ON ks.KeySkillID = cks.KeySkillID
GROUP BY c.ContactID, c.Name;

您还可以完全控制排序和筛选(带有索引)和数据完整性(没有重复或键入等)。

添加/删除技能就像插入/删除行一样简单，而不必执行任何字符串操作。

如果你决定重新命名一项技能，比如。带有“用户界面”的"UI“--同样，在一个设计正确的数据库中，这也是非常容易的：

UPDATE  dbo.KeySkill
SET     Name = 'User Interface'
WHERE   Name = 'UI';

因为您现在已经分离了所有数据，所以您可以确定在更新UI时没有副作用，因为这是唯一存储在该字段中的值。

Working Demo on db<>fiddle

如果您无法控制您的设计，并且无法进行这些更改，那么以下内容应该适用于您：

STUFF(key_skill, 
        CHARINDEX(CONCAT(';', @current, ';'),CONCAT(';', key_skill, ';')), 
        LEN(@current), 
        @replace);

前提是，如果将;添加到key_skill字符串和@current参数的开始和结尾，那么不管该术语是在字符串的开头还是结尾，您都将在;UI;PHP;Building;中查找;UI;，因此搜索项在building中不再匹配。

在这里使用STUFF()比使用REPLACE()更容易，这样您就不必在末尾实际构建带有分号的字符串，然后在末尾删除它们。您所需要的只是使用CHARINDEX来找出技能从字符串开始的位置(第二个参数在内容中)，技能的长度(第三个参数)，并使用它作为在(第四个参数)中“填充”新字符串的起点。

Demo

CREATE TABLE #T (Contact VARCHAR(255), key_skill VARCHAR(255));
INSERT  #T(Contact, key_skill)
VALUES
    ('John Doe', 'AI;UI;ONC;BI;PHP'),
    ('Craig Smith', 'UI;PHP;Building'),
    ('Loren Paul', 'AI;UI');

DECLARE @current VARCHAR(50) = 'UI',
        @replace VARCHAR(50) = 'New Skill'

UPDATE  #T
SET key_skill = STUFF(key_skill, 
                    CHARINDEX(CONCAT(';', @current, ';'),CONCAT(';', key_skill, ';')), 
                    LEN(@current), 
                    @replace)
WHERE  CHARINDEX(CONCAT(';', @current, ';'),CONCAT(';', key_skill, ';')) > 0;

SELECT *
FROM #T;

ADENDUM

由于您无法更改数据结构，一个更健壮的方法是解构分隔列表(使用STRING_SPLIT())，然后进行更改，然后再重构它(使用STRING_AGG())。

CREATE TABLE #T (Contact VARCHAR(255), key_skill VARCHAR(255));
INSERT  #T(Contact, key_skill)
VALUES
    ('John Doe', 'AI;UI;ONC;BI;PHP'),
    ('Craig Smith', 'UI;PHP;Building'),
    ('Loren Paul', 'AI;UI');

DECLARE @current VARCHAR(50) = 'UI',
        @replace VARCHAR(50) = 'New Skill'

UPDATE  t
SET     t.key_skill = s.NewList
FROM    #T AS t
        CROSS APPLY
        (   SELECT  STRING_AGG(Value, ';')
            FROM    (   SELECT  Value
                        FROM    STRING_SPLIT(t.key_skill, ';') AS s
                        WHERE   s.value <> @current
                        UNION
                        SELECT  @replace
                        WHERE   @replace <> ''
                    ) AS s

        ) AS s (NewList);

如果没有指定@current值，这将简单地添加技能，如果没有设置@replace，则只需删除@current。

Working Demo on db<>fiddle

附加2

对于不支持STRING_AGG()的Server 2016，可以使用XML扩展作为替代：

DECLARE @current VARCHAR(50) = 'UI',
        @replace VARCHAR(50) = 'New Skill'

UPDATE  t
SET     t.key_skill = STUFF(s.NewList.value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM    #T AS t
        CROSS APPLY
        (   SELECT  CONCAT(';', Value)
            FROM    (   SELECT  Value
                        FROM    STRING_SPLIT(t.key_skill, ';') AS s
                        WHERE   s.value <> @current
                        UNION
                        SELECT  @replace
                        WHERE   @replace <> ''
                    ) AS s
            FOR XML PATH(''), TYPE
        ) AS s (NewList);