Magic Word
Posted on October 2, 2006
Sometimes programming is a challenge in stamina. I wanted to query a table for all records with a string that ended with “_6-1_CI”. So my query was “SELECT * FROM table WHERE column LIKE ‘%_6-1_CI’ and it worked great. Then I noticed that it returned me a record that ended with “_46-1_CI” which was not so great. So after much digging, I learned that the underscore has special meaning in SQL, it is a wildcard for a single character. So I changed my query to say “LIKE ‘%#_6-1#_CI’ ESCAPE ‘#’” but either ADO, VB6 or Access didn’t like the ESCAPE statement. So, after much Googling, I learned that I could enclose my special characters in square brackets, and wallah, it worked. So “LIKE ‘%[_]6-1[_]CI’” is the magic word. That only took me three hours. Sheesh!
update I see that my magic words also affect my blogging software. LOL. All those italics you see aren’t supposed to be there, at least, I didn’t want them there. tsk tsk.
Leave a Comment
If you would like to make a comment, please fill out the form below.