Thank you for your response.

I tried to create a function that can be used in a query:

CREATE OR REPLACE FUNCTION idno_filter_test(idno STRING, filter_value STRING)
RETURNS BOOLEAN
RETURN IF(current_user() = "admin@test.com", idno = filter_value,
    concat(left(idno, 2), "**", right(idno, 2)) = concat(left(filter_value, 2), "**", right(filter_value, 2)) )

However, this may result in errors in the query. For example, with the following condition:

SELECT * 
FROM working.idno_masked_downstream
WHERE idno_filter_test(memidno, "123456")

Both "123456" and "124356" match the condition id=12**56, so it may return two results.

Additionally, if I want to join two tables using idno as the key, how should I configure the masked table to ensure it can be matched with other tables based on the key value?

SELECT aa.order_number, aa.IDNO, bb.MEMIDNO
FROM processed.order_integrate AS aa
INNER JOIN working.idno_masked_downstream AS bb
ON bb.MEMIDNO = aa.IDNO

I would greatly appreciate any guidance on how to resolve these issues. Thank you for your assistance.