Escape square brackets from Like statement in two table joining

While comparing the data which has square brackets Ex: ‘-[VVE172BUS-3.30]-‘ I am using below

Select * from ServerOps.dbo.tbl_VmHealth_Firmware where biosname like ‘-[[]VVE172BUS-3.30]-‘

AND i am getting the right outout however I need to compare biosname ‘-[VVE172BUS-3.30]-‘ with another column from different table. Could you pls help me how to escape the [] while joining two columns

SELECT VmhostName, ParentName, DataCenterName,VcenterName,
                VF.Manufacturer, VF.Model, VF.BiosVersion, MAX(VF.ReleaseId) AS ReleaseId
                FROM ServerOps.dbo.tbl_VmHealth_Vmhost VH WITH (NOLOCK)
                LEFT JOIN ServerOps.dbo.tbl_VmHealth_Firmware VF WITH (NOLOCK)
                    ON VH.Manufacturer LIKE '%' + VF.Manufacturer + '%' AND VH.Model = VF.Model
                    AND VH.BiosVersion LIKE  '%' + VF.BiosVersion + '%' 
                    AND ((VH.ReleaseDate >= VF.ReleaseDate AND  VH.Manufacturer not like '%Dell%')
                        OR  (VH.Manufacturer like '%Dell%'))
                WHERE (VcenterName IS NOT NULL)
              
                GROUP BY VmhostName, ParentName, DataCenterName, VcenterName, VF.Manufacturer, VF.Model, VF.BiosVersion, VF.BiosVersion

Answer

The problem with square brackets occurs in SQL server, and only with the opening bracket [. This is because we can use square brackets to surround identifiers such as table and column names, which allows us to use names with spaces with out any problem, but means that SQL server sees an opening square bracket as a special character when using LIKE.

--CREATE TWO IDENTICAL TABLES AND INSERT SOME DATA
CREATE TABLE t1 ( biosname VARCHAR(100));
CREATE TABLE t2 ( biosname VARCHAR(100));
INSERT INTO t1 VALUES ('-[VVE172BUS-3.30]-');
INSERT INTO t2 VALUES ('-[VVE172BUS-3.30]-');

--WITH THE OPERATEUR = ALL IS WELL
SELECT * FROM  t1 WHERE biosname = '-[VVE172BUS-3.30]-';

| biosname           |
| :----------------- |
| -[VVE172BUS-3.30]- |

--BUT WITH LIKE WE GET NO RESULT
SELECT * FROM  t1 WHERE biosname LIKE '-[VVE172BUS-3.30]-';

| biosname |
| :------- |

--WE CAN USE AN ESCAPE CHARACTER IF WE DECLARE IT
SELECT * FROM  t1 WHERE biosname LIKE '-[VVE172BUS-3.30]-' ESCAPE '';

| biosname           |
| :----------------- |
| -[VVE172BUS-3.30]- |

--THE SAME RULES APPLY FOR COMPARING 2 FIELDS
--ALL IS WELL IF WE USE =
Select * from t1, t2
where t1.biosname = T2.biosname;

biosname           | biosname          
:----------------- | :-----------------
-[VVE172BUS-3.30]- | -[VVE172BUS-3.30]-

--TO USE ESCAPE LIKE WE HAVE THE REPLACE '[' WITH '[' 
Select * from t1, t2
where t1.biosname like REPLACE(t2.biosname, '[', '[') escape '';

biosname           | biosname          
:----------------- | :-----------------
-[VVE172BUS-3.30]- | -[VVE172BUS-3.30]-

db<>fiddle here