MySQL starts with searching issue

Question

I'm having an issue using the % wildcard with a MySQL query.

http://www.w3schools.com/sql/sql_like.asp

Having read that article, I am using % and not getting quite what I was expecting.

I have a series of values, such as

1_1
1_2
2_1
2_2... etc

including

11_1
11_2

Now, in some cases I want to return specifically those whose value = 11_2, or 1_2 etc. This works fine

WHERE fieldName = '11_2'

etc... as expected

However, in some cases I want to find all items which start with a 1 or all items which start with 11

From the w3Schools link, I was expecting

WHERE fieldName LIKE '1_%'

To find anything that begins with 1_ specifically, therefore, in my example, returning:

1_1
1_2

BUT, it also returns

11_1
11_2

Why is this? And how can I over come it?

Answer 1

Underscore is a wildcard for a single character. You will need to change your SQL to something like:

WHERE fieldName LIKE '1%'

Or you can escape the underscore

WHERE fieldName LIKE '1\_%'