SQL 'LIKE" case sensitivity

[NK]

Hi all,

Does anyone know of how I can disable case sensitivity for the LIKE
function in SQL?

Currently the SQL statement looks like:

$query = "SELECT * FROM itrader_games WHERE console='$console' AND
genre='$genre' AND title LIKE '%$title%' ";

(thanks Tom Thackrey for the above)

Also, I have tried to make it so that if 'any' is selected for the
hardcoded variables (errmm does that make sense?) the valeu of Any is *
and put into the SQL statement - sorry i dont know how to explain this
but this is the HTML used

<option value="*">Any</option>

If anyone can make sense of my drivel and can help it would be greatly
appreciated!

Thanks,
NK

[Andy Hassall]

On Mon, 22 Dec 2003 01:00:48 +1100, NK <NK> wrote:

Does anyone know of how I can disable case sensitivity for the LIKE
function in SQL?
You can't in standard SQL. What database are you using? Specific databases
handle case-insensitivity in different ways.
Also, I have tried to make it so that if 'any' is selected for the
hardcoded variables (errmm does that make sense?) the valeu of Any is *
and put into the SQL statement - sorry i dont know how to explain this
but this is the HTML used

<option value="*">Any</option>

If anyone can make sense of my drivel and can help it would be greatly
appreciated!

Don't you mean '%', which is a wildcard character for SQL LIKE? You're
probably better off leaving the condition out of the SQL entirely rather than
doing LIKE '%' anyway.

--
Andy Hassall (an**@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

[Jerry Sievers]

NK <NK> writes:

Does anyone know of how I can disable case sensitivity for the LIKE
function in SQL?

this would depend on what SQL backend you're using and you didn't say
which.

for postgres it's 'ilike' or also a case insensitive regular
expression such as;

where field ~* 'foo'

matches foo FOO fOO etc...

--
-------------------------------------------------------------------------------
Jerry Sievers 305 854-3001 (home) WWW ECommerce Consultant
305 321-1144 (mobile http://www.JerrySievers.com/

[Michael Fuhr]

NK <NK> writes:

Does anyone know of how I can disable case sensitivity for the LIKE
function in SQL?
LIKE is an operator, not a function. Whether LIKE is case-sensitive
or not depends on the database -- for example, it's case-sensitive
in PostgreSQL but case-insensitive in MySQL. There might be various
ways around that for your database, but the following should work
just about everywhere:

WHERE LOWER(fieldname) LIKE 'pattern'

In this example, 'pattern' would be all lowercase.
Currently the SQL statement looks like:

$query = "SELECT * FROM itrader_games WHERE console='$console' AND
genre='$genre' AND title LIKE '%$title%' ";

(thanks Tom Thackrey for the above)

Also, I have tried to make it so that if 'any' is selected for the
hardcoded variables (errmm does that make sense?) the valeu of Any is *
and put into the SQL statement - sorry i dont know how to explain this
but this is the HTML used

<option value="*">Any</option>

Why include fields you want to ignore in the WHERE clause at all?
You could build the query in several steps and add only those
variables you want to check, like this:

unset($where); // make sure it's empty

if ($console != "*") $where[] = "console = '$console'";
if ($genre != "*") $where[] = "genre = '$genre'";
if ($title != "*") $where[] = "LOWER(title) LIKE LOWER('$title')";

$where_sql = implode(" AND ", $where);

$sql = "SELECT ... WHERE $where_sql";

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

[Michael Fuhr]

mf***@fuhr.org (Michael Fuhr) writes:

Why include fields you want to ignore in the WHERE clause at all?
You could build the query in several steps and add only those
variables you want to check, like this:

unset($where); // make sure it's empty

if ($console != "*") $where[] = "console = '$console'";
if ($genre != "*") $where[] = "genre = '$genre'";
if ($title != "*") $where[] = "LOWER(title) LIKE LOWER('$title')";

$where_sql = implode(" AND ", $where);

$sql = "SELECT ... WHERE $where_sql";

Oops...the last few lines should be more like this, in case no
conditions were added:

$sql = "SELECT ... ";

if (isset($where)) {
$sql .= " WHERE " . implode(" AND ", $where);
}

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

[Terence]

NK wrote:

Hi all,

Does anyone know of how I can disable case sensitivity for the LIKE
function in SQL?

Currently the SQL statement looks like:

$query = "SELECT * FROM itrader_games WHERE console='$console' AND
genre='$genre' AND title LIKE '%$title%' ";

Most RMBMSs have a built-in function to change case, for instance
"ucase()" and php cirtainly has this function.

So ou can do
$query = "SELECT * FROM itrader_games WHERE console='".$console."' AND
genre='".$genre."' AND ucase(title) LIKE '%".strtoupper($title)."%' ";

do yourself a favour, always put variable references outside string
literals. Variables are much easier to spot and you can have a
consistent approach to evaluating dynamic bits.

a word of warning, the internal database ucase() statement will cause
the RDBMS to have to run this function for each record prior to
evaluation. So there will be the associated performance hit.

In cases where this is a problem, you can create a redundant column that
is maintained [with rules] containing duplicate but uppercased values
which can be searched without the need to run this function.

Of course, we've no idea what database you are using so no one can
really help you there. I imagine some systems may have a setting
somewhere to set case-insensitivity globally, but we don't know what
database system you are running. We also have no idea what the name of
the uppercasing function may be becuase you haven't told us the name of
the database you are using...