How to bind% and column name in LIKE statement

Question

I am trying to do this:

SELECT FacilityID, FacilityName, CMSProviderID, [Provider Number] FROM G2_Facility, SCIPHospitalCompare WHERE [Provider Number] LIKE '%' + CMSProviderID + '%'; 

And I get:

 Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value '%' to data type int. 

What am I doing wrong? I have seen many examples of such things, but for some reason just doesn't work.

Thanks!

Answer 1

I assume that either [Provider Number] or CMSProviderID is a number field?

Convert a number field to a string to compare two:

 SELECT FacilityID, FacilityName, CMSProviderID, [Provider Number] FROM G2_Facility, SCIPHospitalCompare WHERE CAST([Provider Number] as nvarchar(50)) LIKE '%' + CAST(CMSProviderID as nvarchar(50)) + '%';