Oops... SQL Server Like statement and brackets
I've come across some thing a little *duh* and thought I'd put it up here to help me remember better in the future.
I had a small test application that allowed a user to upload an image, the that image was copied and resized, each resizing saved with a naming convention that would make it easy for the application to work with.
Example:
I upload my_image.jpg
The application creates:
- [large]_my_image.jpg
- [medium]_my_image.jpg
- [small]_my_image.jpg
Using brackets is not something I normally do in naming conventions and from now on will not do again. I really added it as it seemed to help visually when looking at my file system through Windows Explorer. Call it a brain fart, it just suited at the time.
The problem arose when I then went and tried running SQL with a LIKE statement in the WHERE clause.
Example:
select ID
from mytable
where imageName LIKE '[large]_%'
I had not read the SQL Server LIKE statement details, therefore had not realised that using brackets in a LIKE statement actually tries to invoke wild card pattern matching on the part of the string between the brackets. Also, using an underscore calls for an 'any single character' pattern match.
Note: Microsoft SQL Server 2008 Books Online: LIKE - also checked back to the 2000 version and it does not seem anything has changed with this statement's syntax
So yeah, moral of the story, keep it simple.
There are lots of ways to make names unique and named within a convention. I do prefer using a UUID or a timestamp in the file name.
So I might end up with something like:
- small_{UUID}.jpg
- small_{ddmmyyhhmmss}.jpg
Then if you do need to run an SQL LIKE statement you could just use:
select ID
from mytable
where imageName LIKE 'small%'
Anyway, just a note really for myself, but it's always nice if it help someone else.
 |
Oops... SQL Server Like statement and brackets
