如何从SSMS数据库的字符串中提取多个用户名并存入临时表

核心思路 #

先按分号拆分原始字符串，过滤出包含用户信息的片段，再从每个片段中提取-和:之间的用户名，最后插入临时表。

适用于SQL Server 2016及以上版本（支持STRING_SPLIT） #

-- 定义原始字符串变量
DECLARE @str NVARCHAR(MAX) = 'Sent for processing:1 ;DK A/C-WestlySnipes:ACCT NOT FOUND ;DK A/C-SonyaBlade:ACCT NOT FOUND';

-- 创建临时表存储用户名
CREATE TABLE #TempUsers (UserName NVARCHAR(100));

-- 提取用户名并插入临时表
INSERT INTO #TempUsers (UserName)
SELECT 
    SUBSTRING(
        LTRIM(RTRIM(s.Value)),
        CHARINDEX('-', LTRIM(RTRIM(s.Value))) + 1,
        CHARINDEX(':', LTRIM(RTRIM(s.Value))) - CHARINDEX('-', LTRIM(RTRIM(s.Value))) - 1
    ) AS UserName
FROM STRING_SPLIT(@str, ';') AS s
WHERE LTRIM(RTRIM(s.Value)) LIKE 'DK A/C-%'; -- 过滤仅保留用户信息行

-- 验证结果
SELECT * FROM #TempUsers;

-- 用完后可删除临时表
-- DROP TABLE #TempUsers;

适用于SQL Server 2016以下版本（自定义拆分函数） #

如果你的SQL Server版本不支持STRING_SPLIT，先创建一个自定义字符串拆分函数：

CREATE FUNCTION dbo.SplitString (@str NVARCHAR(MAX), @delimiter CHAR(1))
RETURNS @result TABLE (Value NVARCHAR(MAX))
AS
BEGIN
    DECLARE @start INT = 1;
    DECLARE @end INT;
    WHILE CHARINDEX(@delimiter, @str, @start) > 0
    BEGIN
        SET @end = CHARINDEX(@delimiter, @str, @start);
        INSERT INTO @result (Value)
        SELECT SUBSTRING(@str, @start, @end - @start);
        SET @start = @end + 1;
    END
    INSERT INTO @result (Value)
    SELECT SUBSTRING(@str, @start, LEN(@str) - @start + 1);
    RETURN;
END;

然后用该函数替代STRING_SPLIT执行提取：

DECLARE @str NVARCHAR(MAX) = 'Sent for processing:1 ;DK A/C-WestlySnipes:ACCT NOT FOUND ;DK A/C-SonyaBlade:ACCT NOT FOUND';
CREATE TABLE #TempUsers (UserName NVARCHAR(100));

INSERT INTO #TempUsers (UserName)
SELECT 
    SUBSTRING(
        LTRIM(RTRIM(s.Value)),
        CHARINDEX('-', LTRIM(RTRIM(s.Value))) + 1,
        CHARINDEX(':', LTRIM(RTRIM(s.Value))) - CHARINDEX('-', LTRIM(RTRIM(s.Value))) - 1
    ) AS UserName
FROM dbo.SplitString(@str, ';') AS s
WHERE LTRIM(RTRIM(s.Value)) LIKE 'DK A/C-%';

SELECT * FROM #TempUsers;
-- DROP TABLE #TempUsers;

内容的提问来源于stack exchange，提问作者NanaDing