473,390 Members | 1,323 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,390 software developers and data experts.

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
Jul 17 '05 #1
5 48294
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)
Jul 17 '05 #2
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/
Jul 17 '05 #3
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/
Jul 17 '05 #4
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/
Jul 17 '05 #5
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...

Jul 17 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

16
by: Starwiz | last post by:
I'm a VB.net programmer, and I'm about to start working with two C++ programmers and teach them .net. I've decided to use C# in teaching them, since it's similar enough to VB.net that I can read...
5
by: Jeff S | last post by:
I'm getting started with C# and was just wondering if C# veterans LIKE the fact that the language is case sensitive. If so, how is case sensitivity helpful? Do you create multiple variables,...
3
by: Jason Tesser | last post by:
I am converting data from Access into Postgres and ran into an issue with case sensitivity. Can I write queries in Access that will be case insensitive without rewriting the queries. So I would...
14
by: Christian Sell | last post by:
Hello, I am running into a problem with PGs case sensitivity with regard to column and table names. I am using program components that require the object names returned from database metadata...
15
by: gregory_may | last post by:
Is there any options in VS 2005 to better handle case issues in C# (Similar to VB.Net)?
8
by: Chris Noble | last post by:
I need to check whether a particular user is already a member of an Active Directory Security Group. The following code extract works but only if the user distinguished name is exactly the same...
2
by: sweetpotatop | last post by:
Hi, I believe my SQL server was configured as Case sensitivity. I have a number of stored procedures which were moved from a non-Case sensitivity SQL server. Because of the Case sensitivity, I...
7
by: MLH | last post by:
If I drop Like "*ABC*" in a QBE grid criteria cell, the records returned include mixed case. Can I force the uppercase limitation in a QBE grid?
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.