CODEWITHSUNDEEP
sqlsql-servert-sql
Stephanus DJ
Stephanus DJ

Reputation: 109

In SQL. Why does this 'Like' statement with a wildcard not work?

Declare @Temp1 as nvarchar(max) = '[10004120][1100][10033583][1005]'
Declare @Temp2 as nvarchar(max) = '[10004120][1100]'

If @Temp1 like @Temp2 + '%'
    Print 'Yup'

Why does this not work? I don't get the "yup" message.

Upvotes: 5

Views: 3054

Answers (4)

Bacon Bits
Bacon Bits

Reputation: 32230

Rather than add a specific pattern for an open bracket, I would use the optional ESCAPE clause for LIKE expressions:

Declare @Temp1 as nvarchar(max) = '[10004120][1100][10033583][1005]'
Declare @Temp2 as nvarchar(max) = '[10004120][1100]'

If @Temp1 LIKE REPLACE(@Temp2, '[', '\[') + '%' ESCAPE '\'
    Print 'Yup'

To me that's cleaner and more explicit about what you're trying to accomplish. Obviously, you've got to pick an escape character that's guaranteed not to be in your data or otherwise use @Temp1 LIKE REPLACE(REPLACE(@Temp2, '\', '\\'), '[', '\[') + '%' ESCAPE '\' to escape embedded escape characters first.

Upvotes: 2

LukStorms
LukStorms

Reputation: 29677

At least in MS Sql Server, the square brackets have meaning in a LIKE.
They are a bit similar to a character class in regex.

For example:

WHERE col LIKE '[0-9]%' will find col values that start with a digit.
WHERE col LIKE '%[^0-9]' will find col values that end with anything but a digit.

So something as a LIKE '[10004120][1100]%' is equivalent to a LIKE '[0-24][01]%' and will actually match with strings like '11X', '40Y'.

But in this case you don't have to use LIKE.
Since the first string just has to start with the second string.

IF @Temp2 = LEFT(@Temp1, LEN(@Temp2))
PRINT 'yup'

And to check if the second string is a part of the first string.
You could use CHARINDEX. Which will return 1 if it's found at the start.

IF CHARINDEX(@Temp2, @Temp1) > 0
BEGIN 
   PRINT 'yup'
END

Upvotes: 2

Laughing Vergil
Laughing Vergil

Reputation: 3766

This doesn't work because the brackets in the string have a special function in a LIKE statement - items between the brackets constitute a set of values that the singular character at the specified position matches. Your original pattern looks for a 1,0,4, or 2 followed by a 1 or an 0. To make this work, you should have a pattern like this:

Declare @Temp2 as nvarchar(max) = '[[]10004120][[]1100]'

Upvotes: 8

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You have the like backwards. The pattern is the second operand. The logic should be:

 @temp1 like @temp2 + '%'

Upvotes: 2

Related Questions