1

I have the following data in TableA...

ID | Text
---------------------------------------------
1  | let's find this document
2  | docments are closed

...and if I do the following select...

select Text from TableA where Text like '%doc%';

...I seem to get a strange result. Both rows are returned. With this select, should it not only return row 1? I would have thought that..

select Text from TableA where Text like 'doc%';

...would have returned just row 2. Am I missing something?

What I'm trying to do is run 3 separate searches across this data as part of my searching tool. The first match is to look for the specified pattern "doc" at the beginning of a string, secondly, my next match looks for the same pattern but at the end of a string, and thirdly, identify if the pattern appears anywhere within the text - so can have text surrounding it. Ideally, the first search would only match row 2, the second search would return no results and the third result would only return row 1.

The reason for doing it like this is I wanted to try and get a feel for how the pattern matched the string. Would make it easier to read the results to know that the pattern for a given row matched either (a) at the beginning, (b) at the end, (c) anywhere in the middle.

Had thought about using regexp, but my data is unicode.

Aldwoni
  • 1,168
  • 10
  • 24
user1236443
  • 549
  • 2
  • 8
  • 19
  • What are you trying to accomplish? You basically stated what MySQL does - which is nothing out of the ordinary. Perhaps if you explain what your end goal is, we can answer what you are trying to do. – Duniyadnd Jan 16 '13 at 13:58
  • 2
    % is a substitute for any character - that includes *no* characters. Hence as far as `%doc` knows " doc" is the same as "doc" – George Jan 16 '13 at 13:58
  • Updated my original post with more on my end goal. – user1236443 Jan 16 '13 at 14:11
  • What if it was any character but not "no" characters. I'm thinking in terms of the combined results of all three match exercises, which would lead to confussion as row 1 and 2 would match both test 1 and 3. I'd get questions as to how "documents are closed" could be a "match at the beginning of string" and "match any where within the string". – user1236443 Jan 16 '13 at 14:15

2 Answers2

3

No, the first query returns both rows, because % means 0 or more characters. So if doc is the first thing appearing in the field, it matches the %doc% pattern as well.

But you're right on the second query, it will only return row 2.

xlecoustillier
  • 16,183
  • 14
  • 60
  • 85
1
  1. doc_% should match it at the beginning, having at least one character after it.
  2. %_doc should match it at the end, having at least one character before it.
  3. %_doc_% should match it anywhere, having at least one character before and after it.

Note that these strict criteria fail to find the exact string "doc", i.e. with nothing before or after it. You may want to include this case in, say, query #1, by loosening it:

  1. doc% should match it at the beginning, having any number of characters after it.
Timo
  • 7,992
  • 4
  • 49
  • 67