On SQL Server 2008 you can create a set-based ordered string splitting function like this:

CREATE FUNCTION dbo.string_split_ordered
(
    @List  nvarchar(max),
    @Delim nvarchar(255)
)
RETURNS TABLE
AS
    RETURN ( SELECT [Value], idx = RANK() OVER (ORDER BY n) FROM 
      ( 
        SELECT n = Number, 
          [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],
          CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))
        FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)
          FROM sys.all_objects) AS x
          WHERE Number <= LEN(@List)
          AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim)) = @Delim
      ) AS y
    );

This function returns every element in the list ( Value ) along with its position in the list ( idx ).

Then you can do this (using a variable):

DECLARE @List varchar(max) = '33,E4,00,23,H3,A9';

SELECT w.warehouse_id
FROM dbo.Warehouses AS w
INNER JOIN dbo.string_split_ordered(@List, ',') AS s
  ON w.warehouse_id = s.Value
ORDER BY s.idx;

Or this (incorporating the table):

SELECT w.warehouse_id
FROM dbo.Warehouses AS w
INNER JOIN
(
  SELECT s.Value, s.idx FROM dbo.ListOfWarehouses low
  CROSS APPLY dbo.string_split_ordered(low.List, ',') AS s
) AS s
ON w.warehouse_id = s.Value
ORDER BY s.idx;

• Example db<>fiddle

You can do something like this:

SELECT  *
FROM    (
    VALUES  (N'E4')
    ,   (N'A9')
    ,   (N'33')
    ,   (N'98')
    ,   (N'23')
) t (warehouse_id)
ORDER BY NULLIF(-charindex(',' + warehouse_id + ',', ',' + '33,E4,00,23,H3,A9' + ','), 0) DESC

Or alternatively, cross join the warehouse sort table instead of hardcoding list:

SELECT  *
FROM    (
    VALUES  (N'E4')
    ,   (N'A9')
    ,   (N'33')
    ,   (N'98')
    ,   (N'23')
) t (warehouse_id)
cross join (
        select '33,E4,00,23,H3,A9' AS warehouses
    ) ListOfWarehouses
ORDER BY NULLIF(-charindex(',' + warehouse_id + ',', ',' + ListOfWarehouses.warehouses + ','), 0) DESC

The gist of the solution is that find where in the string the warehouse id matches, and then handle non-matches "last".

On SQL Server 2008 you can create a user-defined function

CREATE FUNCTION [dbo].[split_string] 
(   
@str varchar(max)
,@sep varchar(5)=','
)
RETURNS TABLE 
AS
RETURN 
(
with vv as(
select cast('<v><x>'+REPLACE(@str,@sep,'</x><x>')+'</x></v>' as xml) a
)
select t.v.value('.[1]','varchar(max)') val
from vv
cross apply a.nodes('v/x') t(v)
)

Usage:

declare @warehouses table(id varchar(10))
insert @warehouses (id)
select val from dbo.split_string
('E4,A9,33,98,23',',')

select w.* from dbo.split_string('33,E4,00,23,H3,A9',',') ww
inner join @warehouses w on w.id=ww.val

Result:

33
E4
23
A9

All parts seem to be 2 position long:

DECLARE @warehouses varchar(200) ;
SET @warehouses = '33,E4,00,23,H3,A9';

select x,substring(@warehouses,(x-1)*3+1,2) as w
from (select 1 as x union all select 2 union all select 3 union all 
      select 4 union all select 5 union all select 6) x

• The subquery, which now produces the values from 1 - 6, can be made more variable too, when more values are possible than 6.

Please try the following solution based on XML and XQuery.

No need in a user-defined function (UDF).

SQL

-- DDL and sample data population, start
DECLARE @tbl_Warehouses TABLE (ID INT IDENTITY PRIMARY KEY, warehouse_id VARCHAR(2));
INSERT @tbl_Warehouses (warehouse_id) VALUES
('E4'), ('A9'), ('33'), ('98'), ('23');

DECLARE @tbl_ListOfWarehouses TABLE (ID INT IDENTITY PRIMARY KEY, warehouses VARCHAR(100));
INSERT @tbl_ListOfWarehouses (warehouses) VALUES
('33,E4,00,23,H3,A9');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = ',';

WITH rs AS
(
    SELECT t.*
        , x.value('(./text())[1]', 'CHAR(2)') AS warehouse_id
        , ROW_NUMBER() OVER (ORDER BY @@SPID) AS seq
    FROM @tbl_ListOfWarehouses AS t
    CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' + 
        REPLACE(warehouses, @separator, ']]></r><r><![CDATA[') + 
        ']]></r></root>' AS XML)) AS t1(c)
    CROSS APPLY c.nodes('/root/r') AS t2(x)
)
SELECT rs.warehouse_id 
FROM rs INNER JOIN @tbl_Warehouses AS w
    ON rs.warehouse_id = w.warehouse_id
ORDER BY rs.seq;

Output

If we can assume from your sample data warehouse_id is always 2 characters then the following simple criteria might work for you:

declare @List Varchar(100)= '33,E4,00,23,H3,A9'

select * 
from dbo.warehouses
cross apply (values(NullIf(CharIndex(warehouse_id + ',', @List + ','), 0) / 3 + 1))l(p)
where p is not null
order by p;

Demo Fiddle ;