I need to be able to check if a variable has a certain string of text inside it. Here's what I have now:
--This sample always goes to the ELSE block.
IF( @name LIKE '%John%' )
BEGIN
--do one thing
END
ELSE
BEGIN
--do the other thing
END
views:
56answers:
2
+2
A:
dunno...works for me
declare @name varchar(45)
set @name = 'johnson'
IF( @name LIKE '%John%' )
BEGIN
print 'like'
END
ELSE
BEGIN
print 'dislike'
END
Jeremy
2010-04-21 20:25:40
Aha! My problem was that I declared the variable as a plain varchar, not varchar with a length like you did. Thanks!
2010-04-21 20:29:02
I'll mark this as a answer once my account allows me to. (I'm brand new here)
2010-04-21 20:29:57
@user322652 - yep, you need to be careful to always give a size. In this scenario, it would default to 1. In other scenarios, the length if not defined would be 30! (I wrote up about this recently here if you're interested: http://www.adathedev.co.uk/2010/04/sql-cast-to-varchar-without-size.html)
AdaTheDev
2010-04-21 20:31:41
@user322652: Guess you're able now
abatishchev
2010-04-21 20:32:01
you can use `NVARCHAR(MAX)` and it's better always use Nvachar instead of just varchar
abatishchev
2010-04-21 20:32:49
@abatishchev - have to disagree with that. If you don't need to store unicode data, then you shouldn't choose NVARCHAR - uses 2 bytes per character instead of 1, which is unneccessary if not needed!
AdaTheDev
2010-04-21 20:39:23
@AdaTheDev Thanks.
2010-04-21 20:45:37
+1
A:
The syntax is fine. If it isn't working for you, it could be down to the collation of your database.
If you have a case sensitive collation, then it will NOT match unless the case matches. e.g. if @name is 'Something john said', then a LIKE searching for "John" will not find a match, hence will go to the ELSE.
AdaTheDev
2010-04-21 20:29:48