I have a huge table that has a char(80) name column (indexed).
The name pattern is like
A_B_C-D.123.456.pdf
A_B_C-D.123.333.doc.2
When I select name from table where name like 'A_B%', it was doing a table scan;
I guess the underscore "_" was treated like a special character, instead of a character "_".
When I use select name from table where name like 'A\\_B%', it was doing an index scan with my result returned very fast, but I got a warning about the \\:
WARNING: nonstandard use of \\ in a string literal
LINE 1: ...elect name from table where file_name like 'A\\_B...
^
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
By the way, the wild card works if it is at the end of the query, it does not seem to work if it is in the middle:
select name from table where name like 'A\\_B%' -- fine;
select name from table were name like 'A\\_B%.pdf'; -- returns nothing.
questions:
1. How do I get rid of the nonstandard warning, but still using the index search?
2. How do I search with a wild card % in the middle of the pattern? Would varchar(80) or char(80) make a difference about the wild card search (% in the middle)?
Thanks!
Need a vacation?
Get great deals to amazing places on Yahoo! Travel.