I have a stored procedure that searches a table on a column by a string I pass into a varchar.
This works, returns the specific record with the correct ING_LOC_DESCRIPTION
DECLARE @strSearch VARCHAR(500)
SET @strSearch = 'Care1st LETTER Location'
SELECT TOP 10 ING_LOC_DESCRIPTION
FROM [CDRS].[ING_LOC_TRANS_MASTER]
WHERE [ING_LOC_DESCRIPTION] LIKE '%' + @strSearch + '%'
ORDER BY [ING_LOC_ID]
This doesn't work returns the top ten results from all records ordered by ING_LOC_ID:
DECLARE @strSearch VARCHAR(500)
SET @strSearch = '[{"WorkFlowID":"MoveFile"}]'
SELECT TOP 10 ING_LOC_DESCRIPTION
FROM [CDRS].[ING_LOC_TRANS_MASTER]
WHERE [ING_LOC_DESCRIPTION] LIKE '%' + @strSearch + '%'
ORDER BY [ING_LOC_ID]
Are any of these characters [, {, ", :, }, ] invalid in a varchar? Is it being interpreted as 2 strings? Or is it a problem with the LIKE keyword?
The string assignment seems to be ok because
DECLARE @strSearch VARCHAR(500)
SET @strSearch = '[{"WorkFlowID":"MoveFile"}]'
SELECT @strSearch
returns
[{"WorkFlowID":"MoveFile"}]
Unfortunately
DECLARE @strSearch VARCHAR(500)
SET @strSearch = '[{"WorkFlowID":"MoveFile"}]'
SELECT @strSearch LIKE '[{"WorkFlowID":"MoveFile"}]'
Does not return true or false as I had hoped, it returns an error
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'LIKE'
I think LIKE can only be used in a WHERE clause so I don't think that proves a problem with using LIKE.
Yes. In a LIKE, the [/] bounds a character class. So the code matches any value that contains { or " or W and so on.
I would recommend using a different method:
SELECT TOP 10 ING_LOC_DESCRIPTION
FROM [CDRS].[ING_LOC_TRANS_MASTER]
WHERE CHARINDEX(@strSearch, ING_LOC_DESCRIPTION) > 0
ORDER BY [ING_LOC_ID];
No wildcards in a simple string match.