SQL Server 2016

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE products
             (productid uniqueidentifier);
INSERT INTO products
            (productid)
            VALUES ('A9472294-CFDD-40AC-BC2D-00E39AF4A300'),
                   ('9A817E40-E4B1-4487-A376-010DD6377E38'),
                   ('078A3C75-C442-4D88-A1E0-0118B8706667');
3 rows affected

SELECT p.*
       FROM products p
            INNER JOIN (SELECT arg_xml_node.xml_node.value('(.)[1]', 'uniqueidentifier') uniqueidenfier
                               FROM (SELECT convert(xml,
                                            concat('<x>',
                                                   replace('A9472294-CFDD-40AC-BC2D-00E39AF4A300, 9A817E40-E4B1-4487-A376-010DD6377E38, 078A3C75-C442-4D88-A1E0-0118B8706667',
                                                           ', ',
                                                           '</x><x>'),
                                                   '</x>')) xml) arg_xml
                                    CROSS APPLY arg_xml.xml.nodes('x') arg_xml_node (xml_node)) arg_uniqueidenfier
                       ON arg_uniqueidenfier.uniqueidenfier = p.productid;


productid


a9472294-cfdd-40ac-bc2d-00e39af4a300

9a817e40-e4b1-4487-a376-010dd6377e38

078a3c75-c442-4d88-a1e0-0118b8706667


SELECT p.*
       FROM products p
            INNER JOIN (SELECT convert(uniqueidentifier, ltrim(value)) uniqueidenfier
                               FROM string_split('A9472294-CFDD-40AC-BC2D-00E39AF4A300, 9A817E40-E4B1-4487-A376-010DD6377E38, 078A3C75-C442-4D88-A1E0-0118B8706667',
                                                 ',')) arg_uniqueidenfier
                       ON arg_uniqueidenfier.uniqueidenfier = p.productid;


productid


a9472294-cfdd-40ac-bc2d-00e39af4a300

9a817e40-e4b1-4487-a376-010dd6377e38

078a3c75-c442-4d88-a1e0-0118b8706667