August 6, 2003 at 5:50 am
I am getting incorrect results from the following SELECT statement:
SELECT ProductCode
FROM Products
WHERE ProductDescription LIKE '%[)-+]%'
I expect the query to return all the product codes for which the ProductDescription column contains either ‘)’ or ‘-‘ or ‘+’. The actual result I’m getting is all product codes with product descriptions that contain an underscore character. I know that there are product descriptions containing at least one of these 3 characters.
If I rearrange the order of the characters in the square brackets to: -)+ then the expected results are returned. If I rearrange the order of the characters to +-) then I get no results back.
Does anyone know what is happening?
I’ve read Books Online but haven’t found anything that explains this.
Thanks.
Keith
August 6, 2003 at 6:58 am
When you have a minus sign "-" in the pattern then SQL thinks it's a range. ie. "[)-+]" means any character in the range ")" through "+".
Specifying "[-)+]", as you did, changes the meaning of the pattern to any one of "-", ")' or "+"
Cheers,
- Mark
Cheers,
- Mark
August 6, 2003 at 7:27 am
Actually, BOL does explain it. Just not very well.
The hyphen '-' between two characters MEANS between.
In the BOL, use Index tab, go to LIKE. There is a table with descriptions. Go to the part that shows []. The example is [C-P]arsons. The explanation is: it will return all names that end in arsons, and have the beginning character BETWEEN C and P.
Effectively, you were saying all symbols between ) and +. Take a look at your keyboard..underscore is between the two.
So, if you want to use a hyphen in a LIKE and are putting it in square brackets, you need to put it at the beginning (or the end should also work).
-SQLBill
August 6, 2003 at 7:52 am
Thanks guys. I've got the page from BOL printed out in front of me and now that you've said it I can clearly see it!
Keith
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply