How to split one column with array merge value to array and join other table then merge back

There’s a table WaitCheckBil like :

| BIL_NO | USRS | DATE |
| --- | --- | --- |
| XC20090015 | ;STO09;STO02;STO04;STO06;STO10;STO07;STO10; | 2020-09-15 00:00:00.000 |
| XC20090016 | ;STO09;STO02;STO04;STO06;STO10;STO07;STO10; | 2020-09-15 00:00:00.000 |
| XC20090017 | ;STO09;STO02;STO04;STO06;STO10;STO07;STO10; | 2020-09-15 00:00:00.000 |
| XC20090018 | ;STO09;STO02;STO04;STO06;STO10;STO07;STO10; | 2020-09-16 00:00:00.000 |

and a table Users like :

| USR | NAME |
| --- | --- |
| STO02 | John |
| STO04 | Eva |
| STO06 | Lisa |
| STO07 | Boke |
| STO09 | Nii |
| STO10 | Alisa |

Expected result :

| BIL_NO | USRS | DATE |
| --- | --- | --- |
| XC20090015 | ;Nii;John;Eva;Lisa;Alisa;Boke;Alisa; | 2020-09-15 00:00:00.000 |
| XC20090016 | ;Nii;John;Eva;Lisa;Alisa;Boke;Alisa; | 2020-09-15 00:00:00.000 |
| XC20090017 | ;Nii;John;Eva;Lisa;Alisa;Boke;Alisa; | 2020-09-15 00:00:00.000 |
| XC20090018 | ;Nii;John;Eva;Lisa;Alisa;Boke;Alisa; | 2020-09-16 00:00:00.000 |

What I’ve tried & think :

It’s a old system’s table , so I can’t change the struct.
And the table logic is select * from WaitCheckBil where usrs like '%;' + @usr + ';%'; to get user’s wait check bil_NO.

I think it could split ; usrs column value to array , then join with users table on user column, then merge back to users column.

But I have trouble on here , I have no idea how to implement the logic on sql-server 2008.

Online demo link : SQL Server 2017 | db<>fiddle

Answer

OK so here’s a UDF for SQL Server 2008:

CREATE FUNCTION ufnReplaceUIDs(@inUIDs varchar(100))
RETURNS varchar(100)
AS   
--  Replace User Ids with User Names
BEGIN  
    DECLARE @UID varchar(10);
    DECLARE @outUNames varchar(100);  
    DECLARE @outUName varchar(10);
    DECLARE @String varchar(100);
    SET @String = SUBSTRING(@inUIDs, 2, LEN(@inUIDs) - 1)  -- Remove First/Last ; 
    WHILE (CHARINDEX(';', @String) > 0)
        BEGIN
            SET @UID = LEFT(@String, CHARINDEX(';', @String) - 1);
            SELECT @OutUName = [NAME] FROM Users WHERE [USR] = @UID; 
            SET @OutUNames = CONCAT(@OutUNames, ';', @OutUName); 
            SET @String = SUBSTRING(@String, CHARINDEX(';', @String) + 1, 100);
        END;
    RETURN CONCAT(@outUNames, ';');  
END; 

Use it like so: SELECT [BIL_NO], dbo.ufnReplaceUIDs([USRS]) AS [USRS], [DATE] FROM WaitCheckBil and the result is: