Dranes Dranes - 1 month ago 12
SQL Question

SQL Server 2012 - Using REPLACE + Regex in a SELECT column to format the value

I have a table containing values like this:

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


And I would like to remove the "[number];#" and replace it for a single dash "-" for every occurrence

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


Is this possible with default SQL Server 2012 functions?

Thanks!

Answer

You can use PATINDEX and stuff for doing this... but I am still not looking for options without looping...

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, ';#;#', ' - '), ';#', '')