How to split varchar and looping it then merge to varchar with ' , ' separator

i have data varchar like this
'201,203'

i want split and want get data from relation other table like this

i want the result is varchar and split result with separators coma ‘, ‘ like this
room A, room B

>Solution :

You may first construct a function similar to this.

CREATE FUNCTION roomSplit (@roomCode VARCHAR(MAX), @separator VARCHAR(10))
RETURNS VARCHAR(MAX)
AS  
BEGIN 
    DECLARE @res VARCHAR(MAX)
    
    SELECT @res = STUFF((
        SELECT
        ', ' + roomNameColumn
        FROM
        STRING_SPLIT(@roomCode, @separator) AS SPLIT
        INNER JOIN roomTable AS R ON SPLIT.value = R.roomCodeColumn
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
    
    RETURN @res
END

Then you call function that you have created by filling parameters with your data and your separator data.

SELECT dbo.roomSplit('203,201', ',')

Let’s hope it can and does.