SELECT in two tables, with comma-separated IDs

In this case, you can create a FUNCTION that returns a string with the name of the colors passing the IDS you want, as follows.

create FUNCTION StringListToTexto
(
    @List varchar(MAX)
)
RETURNS varchar(max)
AS
BEGIN
    DECLARE @item varchar(800), @Pos int

    SET @List = LTRIM(RTRIM(@List))+ ','
    SET @Pos = CHARINDEX(',', @List, 1)

    declare @texto varchar(max) = '';

     ---- sua tabela substituar por sua tabela
    declare @tbl_coresAux TABLE (id INT, cor VARCHAR(50))
    INSERT INTO @tbl_coresAux VALUES 
    (1  ,' azul'),
    (2  ,' verde'),
    (3  ,' amarelo'),
    (4  ,' vermelho')
    -----

    WHILE @Pos > 0
    BEGIN
        SET @item = LTRIM(RTRIM(LEFT(@List, @Pos - 1)))
        IF @item <> ''
        BEGIN
            declare @IdAux int = (CAST(@item AS int));

            set @texto = @texto +  (Select cor from @tbl_coresAux where id = @IdAux);
        END
        SET @List = RIGHT(@List, LEN(@List) - @Pos)
        SET @Pos = CHARINDEX(',', @List, 1)
    END

    RETURN @texto
END

And you would call it the following way.

declare @tbl_cores TABLE (id INT, cor VARCHAR(50))
INSERT INTO @tbl_cores VALUES 
(1  ,' azul'),
(2  ,' verde'),
(3  ,' amarelo'),
(4  ,' vermelho')

declare @tbl_mesa TABLE (id INT, cores VARCHAR(50))
INSERT INTO @tbl_mesa VALUES 
(1  ,'2,4'),
(2  ,'1,3'),
(3  ,'2,3,4,1')



select id, ([dbo].[StringListToTexto] (cores)) from @tbl_mesa
where id = 2

The ideal even if you have access and can change the structure of this bank, would use a relationship many to many. That way you could map correctly, according to the normal shapes, and would have no problems doing the select in the tables. For example:

tbl_cores
    id | cor
    1  | azul
    2  | verde
    3  | amarelo
    4  | vermelho

tbl_mesa_cores
    id | id_mesa | id_cores
    1  | 1       | 2
    2  | 1       | 4
    3  | 2       | 1
    4  | 2       | 3
    5  | 3       | 2
    6  | 3       | 3
    7  | 3       | 4
    8  | 3       | 1

tbl_mesa
    id | desc
    1  | -
    2  | -
    3  | -

Try this:

select tbl_mesa.id, tbl_cores.cor 
from tbl_mesa inner join tbl_cores
       on ',' + tbl_mesa.cores + ',' like '%,' + cast(tbl_cores.id as nvarchar(20)) + ',%'