CODEWITHSUNDEEP
sqlsql-servert-sqlsql-like
pchancey
pchancey

Reputation: 19

SQL Server 'LIKE' or varchar issue

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.

Upvotes: 0

Views: 2515

Answers (4)

Aaron Dietz
Aaron Dietz

Reputation: 10277

Gordon's answer is probably the neatest way to do this, but here is an example using ESCAPE:

SELECT TOP 10 ING_LOC_DESCRIPTION
FROM [CDRS].[ING_LOC_TRANS_MASTER]
WHERE [ING_LOC_DESCRIPTION] LIKE '%'+@strSearch+'%' ESCAPE '['

You only need to escape one of the square brackets to avoid the brackets binding a character class.

Upvotes: 1

Zohar Peled
Zohar Peled

Reputation: 82524

The [ ] chars has special meaning in T-Sql LIKE - They are used for pattern searches.

For instance: WHERE column LIKE '[a-c]%' will return all records where column starts with a, b or c, while WHERE column LIKE '[^a-c]%' will return all records where Column does not start with a, b or c.

You can escape the [ char by wrapping it inside []: WHERE column LIKE '[[]a]%' will return all records where column starts with [a] (no need to escape the closing bracket).

For more information, read about the arguments of the like operator, and Using Wildcard Characters As Literals a little down the same page.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270993

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.

Upvotes: 3

Pm Duda
Pm Duda

Reputation: 740

You need to escape special characters:

List of special characters for SQL LIKE clause

Pattern Matching with the ESCAPE Clause

Upvotes: 2

Related Questions