How to concatenate % and column name in a LIKE statement
I assume that either [Provider Number] or CMSProviderID is a numeric field?
Convert the numeric field to a string to compare the two:
SELECT FacilityID, FacilityName, CMSProviderID, [Provider Number] FROM G2_Facility, SCIPHospitalCompare WHERE CAST([Provider Number] as nvarchar(50)) LIKE '%' + CAST(CMSProviderID as nvarchar(50)) + '%';