Boolean Nature of LIKE (Simple Problem)
-
Hey, I am a student writing SQL for the first time. Here's my code:
-----------------------------------------------------------------
DECLARE @target VarChar(100)
SET @target = 'Feynman, Richard'
DECLARE @refID Integer
SET @refID = 1
DECLARE @rank Integer
SET @rank = 100
DECLARE @recSubjectString VarChar(4000)
DECLARE @nonRecSubjectString VarChar(4000)
SELECT @recSubjectString = recSubjectString FROM Reference WHERE Reference.RefID=@refID
SELECT @nonRecSubjectString = nonRecSubjectString FROM Reference WHERE Reference.RefID=@refID
PRINT @recSubjectString
DECLARE @newTarget VarChar(100)
SET @newTarget = '['+@target+']'
PRINT @newTarget
--- We look to see if the target matches a starred-browse keyword.
IF(@recSubjectString LIKE @newTarget)
BEGIN
SET @rank = 1
END
PRINT @rank
-----------------------------------------------------------
When ran in Query Analyzer the results look like:
[Feynman, Richard], [Feynman, Richard], 100
Clearly the recSubjectString contains the newTarget. Why doesn't LIKE realize this and return a boolean true? I have little TSQL experience, but I looked closely at the white papers at MSDN and it looked like all the parameters and return types are fine. Thanks for any help!
-
It's due to the brackets "[]".
Like sees the brackets as surrounding a pattern, and therefore actually is ignoring them in the comparison. In your string returned from the table, the brackets are an actual part of the string.
If you change the line where you set @newTarget to:
SET @newTarget = '[[]'+@target+']'
it should work for you. Alternatively, you can strip the brackets from the string being returned from the table.
Note that you have to surround the initial brackets, but not the final one. This is because T-SQL treats the opening bracket differently from the closing bracket. Check out LIKE in BOL, and see the section on using wildcards as literals for more info.
By the way, I hope this wasn't homework.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply