SQL Server 2012 -在SELECT列中使用REPLACE + Regex来格式化值

Question

我有一个包含如下值的表:

+----+------------------------------------------------+
| ID |                   AssignedTo                   |
+----+------------------------------------------------+
|  1 | 92;#Alex Smith;#114;#Joe Day;#184;#Johnny Bone |
+----+------------------------------------------------+

我想删除"[number];#"，并将其替换为单个"-"对于每个出现

+----+------------------------------------+
| ID |             AssignedTo             |
+----+------------------------------------+
|  1 | Alex Smith - Joe Day - Johnny Bone |
+----+------------------------------------+

这是可能的默认SQL Server 2012功能?

谢谢!

Answer 1

您可以使用PATINDEX和其他东西来完成此操作…但我仍然没有寻找没有循环的选项…

DECLARE @var varchar(100) = '92;#Alex Smith;#114;#Joe Day;#184;#Johnny Bone'
WHILE (PATINDEX('%[0-9]%', @var) > 0)
BEGIN
    SELECT
    @var = STUFF(@var, PATINDEX('%[0-9]%', @var), 1, '')
END
SELECT REPLACE(REPLACE(@var, ';#;#', ' - '), ';#', '')

Answer 2

如果名称中没有数字，您可以这样做:

SELECT final.AssignedTo
FROM Tab t
     CROSS APPLY (SELECT REPLACE(t.AssignedTo, '0', '') AS AssignedTo) r0
     CROSS APPLY (SELECT REPLACE(r0.AssignedTo, '1', '') AS AssignedTo) r1
     CROSS APPLY (SELECT REPLACE(r1.AssignedTo, '2', '') AS AssignedTo) r2
     CROSS APPLY (SELECT REPLACE(r2.AssignedTo, '3', '') AS AssignedTo) r3
     CROSS APPLY (SELECT REPLACE(r3.AssignedTo, '4', '') AS AssignedTo) r4
     CROSS APPLY (SELECT REPLACE(r4.AssignedTo, '5', '') AS AssignedTo) r5
     CROSS APPLY (SELECT REPLACE(r5.AssignedTo, '6', '') AS AssignedTo) r6
     CROSS APPLY (SELECT REPLACE(r6.AssignedTo, '7', '') AS AssignedTo) r7
     CROSS APPLY (SELECT REPLACE(r7.AssignedTo, '8', '') AS AssignedTo) r8
     CROSS APPLY (SELECT REPLACE(r8.AssignedTo, '9', '') AS AssignedTo) r9
     CROSS APPLY (SELECT REPLACE(r9.AssignedTo, ';#;#', ' - ') AS AssignedTo) sep
     CROSS APPLY (SELECT REPLACE(sep.AssignedTo, ';#', '') AS AssignedTo) final

CROSS APPLY s只是清理嵌套的REPLACE s。你也可以直接写REPLACE(REPLACE(REPLACE(...), '1', ''), '2', '')...)';#', '')