October 29, 2003 at 3:42 am
Hi!
I have a SELECT which uses a LIKE condition in the WHERE clause, which in some cases can be NULL, but in other cases should search via LIKE. If I use a WHERE like this it works:
WHERE (@FunctionCode IS NULL OR Fncs.FunctionCode LIKE RTRIM(@FunctionCode) + '%')
But if I use this it does not:
IF @FunctionCode IS NOT NULL
SET @FunctionCode = RTRIM(@FunctionCode) + '%'
..
..
WHERE(@FunctionCode IS NULL OR Fncs.FunctionCode LIKE @FunctionCode)
Any tips?
Jonas Hilmersson
Edited by - jonashilmersson on 10/29/2003 03:42:58 AM
BrgdsJonas
October 29, 2003 at 4:16 am
Is the @FunctionCode variable (or parameter) declared long enough to have the '%' appended to it?
If it isn't, the first method would return the correct rows, but the second method wouldn't.
October 29, 2003 at 4:28 am
Ian's answer led my to check the data type, and I had it declared as a char(30). Obviously (!) it makes the LIKE compare to a 30 character length string, even if I had it RTRIM'd before. If I use a varchar(30) it works like I wanted it to, excluding the trailing spaces.
Brgds
Jonas
BrgdsJonas
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply