SQL Server 'LIKE' or varchar issue

Question

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.

SQL Server supports the [bit](http://msdn.microsoft.com/en-us/library/ms177603) data type. (Valid values are 0, 1, 'TRUE' and 'FALSE'.) There is a [boolean](https://msdn.microsoft.com/en-us/library/ms188074.aspx#Anchor_0) data type (with values TRUE, FALSE and UNKNOWN), but you cannot get a firm grip on one: "Unlike other SQL Server data types, a Boolean data type cannot be specified as the data type of a table column or variable, and cannot be returned in a result set." You can use `case when @TestString like @Pattern then 1 else 0 end` to get a `0`/`1` value from a `like` test. — HABO, Aug 10 '18 at 19:34

score 3 · Accepted Answer · answered Aug 10 '18 at 19:26

3

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.

answered Aug 10 '18 at 19:26

Gordon Linoff

1,242,037
58
646
786

Thanks guys I think you've nailed it. – pchancey Aug 10 '18 at 19:39

score 2 · Answer 2 · answered Aug 10 '18 at 19:25

2

You need to escape special characters:

List of special characters for SQL LIKE clause

Pattern Matching with the ESCAPE Clause

answered Aug 10 '18 at 19:25

Pm Duda

741
5
16

score 1 · Answer 3 · answered Aug 10 '18 at 19:27

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.

score 1 · Answer 4 · answered Aug 10 '18 at 19:37

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.

SQL Server 'LIKE' or varchar issue

4 Answers4