Question about LIKE and wildcard operator (%)

Hi all,
Given the following code:
DECLARE @.param nvarchar(100)
SELECT @.param = '[STRING1][STRING2]'
SELECT
CASE WHEN @.param LIKE '%[STRING1]%' THEN 'yes' ELSE 'no' END AS Test1,
CASE WHEN @.param LIKE '%[STRING2]%' THEN 'yes' ELSE 'no' END AS Test2,
CASE WHEN @.param LIKE '%[STRING3]%' THEN 'yes' ELSE 'no' END AS Test3
Why do all 3 statements match successfully?
I only want Test1 and Test2 to match and Test3 to fail...
My end code will be something like (pardon the pun):
SELECT * FROM Table T
WHERE @.param LIKE '%[' + T.ColumnToTest + ']%'
ColumnToTest would contain values such as 'STRING1', 'STRING2', etc...
without the square brackets.
Regards,
Alextry now ;-)
DECLARE @.param nvarchar(100)
SELECT @.param = '[STRING1][STRING2]'
SELECT
CASE WHEN @.param LIKE '%[[STRING1]]%' THEN 'yes' ELSE 'no' END AS
Test1,
CASE WHEN @.param LIKE '%[[STRING2]]%' THEN 'yes' ELSE 'no' END AS
Test2,
CASE WHEN @.param LIKE '%[[STRING3]]%' THEN 'yes' ELSE 'no' END AS
Test3
you have to escape the brackets
http://sqlservercode.blogspot.com/|||Right... nevermind!!
This is one of those D'oh!!! moments (banging head on the desk as I type
this).
I just realised that [] are used for wildcard character matching...
Changed it to using {} and it's all fine now.
Alex
"Alex" <nospam@.hotmail.com> wrote in message
news:OoPHLqRSGHA.4452@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> Given the following code:
> DECLARE @.param nvarchar(100)
> SELECT @.param = '[STRING1][STRING2]'
> SELECT
> CASE WHEN @.param LIKE '%[STRING1]%' THEN 'yes' ELSE 'no' END AS Test1,
> CASE WHEN @.param LIKE '%[STRING2]%' THEN 'yes' ELSE 'no' END AS Test2,
> CASE WHEN @.param LIKE '%[STRING3]%' THEN 'yes' ELSE 'no' END AS Test3
> Why do all 3 statements match successfully?
> I only want Test1 and Test2 to match and Test3 to fail...
> My end code will be something like (pardon the pun):
> SELECT * FROM Table T
> WHERE @.param LIKE '%[' + T.ColumnToTest + ']%'
> ColumnToTest would contain values such as 'STRING1', 'STRING2', etc...
> without the square brackets.
> Regards,
> Alex
>
>|||Because square brackets are delimiters in wildcard searches; they
indicate that the search "Matches any single character within the
specified range or set that is specified inside the square brackets.".
So in your test, you are asking the optimizer if @.param contains any of
the characters S, T, R, I, N, G, (1-3) which, of course, meets all
conditions of your tests.
You'll either need to ESCAPE the square brackets, or don't use them.
Stu