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)) + '%';