[SOLVED] Select with like is not returning the correct results

Select with like is not returning the correct results

I have the following :

CREATE TABLE [dbo].[Table_1]
(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [permitJSON] [varchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO


INSERT INTO Table_1
VALUES ('{  "name" : "Contractor License",
    "group" : "Construction",
    "summary" : "Pending payment for fees. Do not schedule inspection",
    "actions" : [
        "body" : "Issue Date: %permit.issuedate <br/><br/>Cost:24500<br/><br/>Contract Permit:%contract.permitid%<br/><br/>Expires:%permit.expires%"
    ]
}' )
GO

SELECT *
FROM Table_1
WHERE (permitJSON LIKE '%contract_permitid%'   
       OR permitJSON LIKE '%contract_eid%')

The JSON is stored in a varchar(max) column. There are placeholder % signs for variable replacement.

The select query should not return any results since contract_permitid or contract_eid do not actually exist. There is contract.permitid.

This is for SQL Server 2017.

Any ideas?

Solution

Escape the underscore.

SELECT *
FROM Table_1
WHERE permitJSON LIKE '%contract[_]permitid%'  
   OR permitJSON LIKE '%contract[_]eid%'

Alternative with CHARINDEX. (case-insensitive by default, change collation if required.)

SELECT *
FROM Table_1
WHERE CHARINDEX('contract_permitid', permitJSON) > 0  
   OR CHARINDEX('contract_eid', permitJSON) > 0