Selecting ... Where ... Like

PHP
1

Hi,

I want to select data from a table when the field starts with an ‘a’, ‘b’, ‘c’, etc., etc, or ‘z’. Using a letter% is no problem but I need to use a $literal with the ‘%’. Everything I’ve tried does not work. Can someone please give me the syntax I need?

SELECT field FROM table WHERE field LIKE $literal%;

Thanks,

Mike

2

Thanks Scallio, problem solved. I just needed to make some changes from

1))="$literal"
to
1))=" ' . $literal . ' " ')
The literal is lower case but gives me upper and lower cases
3

huh!? I tested it on some test data and get different results for LIKE “a%” (0 rows) and LIKE “A%” (2 rows).

MySQL 5.1.36 @ Win7 (WampServer2)

UPDATE: The column was set to utf8_bin, when I change that to utf8_swedish_ci LIKE behaves as advertised (case insensitive) …

4

mysql LIKE is case insensitive

5

I wouldn’t use LIKE for this, but rather


SELECT
  field
FROM
  table
WHERE
  lower(substring(field,1,1))="$literal"

Make sure $literal is in lowercase. Otherwise you won’t find entries that start with an “A” when looking for “a”

If you do want to use like, the syntax is as follows:


SELECT
  field
FROM
  table
WHERE
  field LIKE "$literal%"

Note that that also doesn’t take case into account (unless you use the same trick with LOWER() I did above)