Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE table_name(ID, name, team, Rooms) AS
SELECT 1235, 'MTAYLOR', 'Blue', 'ZQ123, ZQ456, ZQ654' FROM DUAL UNION ALL
SELECT 3245, 'MTAYLOR', 'Blue', 'ZQ123, 00254, 00859' FROM DUAL;
2 rows affected

SELECT id, name, team, rooms
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER (
           ORDER BY CASE
                    WHEN ', ' || rooms LIKE '%, 00'
                    THEN 1
                    ELSE 2
                    END,
                    id DESC
         ) AS rn
  FROM   table_name t
  WHERE  ', ' || rooms || ', ' LIKE '%, ZQ123, %'
)
WHERE  rn = 1;


ID
NAME
TEAM
ROOMS


3245
MTAYLOR
Blue
ZQ123, 00254, 00859