Column 1
------------------------
Banana, Tomato, Carrot
Orange, Apple, Chicken
Peas, Potatoes, Spinach

Fruit
---------
Banana
Bannana
Pear
Oear

Column 1
------------------
[Fruit][Fruit][Vegetable]
[Fruit][Fruit][Meat]
[Vegetable][Vegetable][Vegetable]

raw_data
(
user_id,
food_list
)

categories
(
  food_name,
  food_category
)

processed_data
(
  user_id,
  food_category,
  food_cnt
)

hospital_data
(
  user_id,
  unknown_name
)

raw_data
(1, Banana, Tomato, Carrot)
(2, Orange, Apple, Chicken)
(3, Peas, Potatoes, pinach)

processed_data
(1, Fruit, 2)
(1, Vegetable, 1)
(2, Fruit, 2)
(2, Meat, 1)
(3, Vegetable, 2)

hospital_data
(3, pinach)

create FUNCTION [dbo].[fnString_DelimeterIndex]
(
    @Text NVARCHAR(4000),
    @Delimiter CHAR,
    @Section SMALLINT
)
RETURNS NVARCHAR(4000)
AS

BEGIN
    DECLARE @NextPos SMALLINT,
        @LastPos SMALLINT,
        @Found SMALLINT,
        @REVERSE BIT

    IF @Section < 0
        SELECT  @Text = REVERSE(@Text)--, @Section=1,@REVERSE=1

    SELECT  @NextPos = CHARINDEX(@Delimiter, @Text, 1),
        @LastPos = 0,
        @Found = 1

    WHILE @NextPos > 0 AND ABS(@Section) <> @Found
        SELECT  @LastPos = @NextPos,
            @NextPos = CHARINDEX(@Delimiter, @Text, @NextPos + 1),
            @Found = @Found + 1


    RETURN  CASE
            WHEN @Found <> ABS(@Section) OR @Section = 0 THEN NULL
            --WHEN @REVERSE =1 THEN
            WHEN @Section > 0 THEN SUBSTRING(@Text, @LastPos + 1, CASE WHEN @NextPos = 0 THEN DATALENGTH(@Text) - @LastPos ELSE @NextPos - @LastPos - 1 END)
            ELSE REVERSE(SUBSTRING(@Text, @LastPos + 1, CASE WHEN @NextPos = 0 THEN DATALENGTH(@Text) - @LastPos ELSE @NextPos - @LastPos - 1 END))
        END
END

insert into #Tbl values('Banana, Tomato, Carrot'),('Orange, Apple, Chicken'),('Peas, Potatoes, Spinach')

insert into #Tbl2 values('Fruit','Banana'),('Fruit','Bananana'),('Vege','Tomato'),('Vege','Carrot')



select '[' + isnull(tbl2.category,'NA') + ']' +
'[' + isnull(tbl2_1.category,'NA') + ']' +
'[' + isnull(tbl2_2.category ,'NA')+ ']'
from #Tbl tbl1
left join #Tbl2 tbl2 ON rtrim(ltrim(dbo.fnString_DelimeterIndex(tbl1.column1,',',1))) = tbl2.column1
left join #Tbl2 tbl2_1 ON rtrim(ltrim(dbo.fnString_DelimeterIndex(tbl1.column1,',',2))) = tbl2_1.column1
left join #Tbl2 tbl2_2 ON rtrim(ltrim(dbo.fnString_DelimeterIndex(tbl1.column1,',',3))) = tbl2_2.column1