Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to concatenate % and column name in a LIKE statement

I'm trying 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 saw many examples doing stuff like this but for some reason it simply does not work at all.

Thanks!

like image 846
Tommy B. Avatar asked Jul 03 '12 19:07

Tommy B.


People also ask

Can you combine like and in statement in SQL?

No, MSSQL doesn't allow such queries. You should use col LIKE '...' OR col LIKE '...' etc.

How do I concatenate two columns in SQL select statement?

SELECT *, CONCAT(FIRSTNAME, LASTNAME) AS FIRSTNAME FROM demo_table; Output: Here, we can see that FIRSTNAME and LASTNAME is concatenated but there is no space between them, If you want to add space between the FIRSTNAME and LASTNAME then add space(' ') in CONCAT() function. This method will change the original table.

How do I concatenate two columns in SQL with brackets?

Example 01: Concatenate Two Columns Using CONCATExecute the SELECT CONCAT command having column names in the brackets separated by a comma. The new column 'Name' is generated to store the concatenated string values in it and the result is shown below.

What is like %% in SQL?

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards often used in conjunction with the LIKE operator: The percent sign (%) represents zero, one, or multiple characters. The underscore sign (_) represents one, single character.


1 Answers

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)) + '%'; 
like image 78
LittleBobbyTables - Au Revoir Avatar answered Oct 07 '22 22:10

LittleBobbyTables - Au Revoir