Get DB-date, ignore Ucase and Lcase

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Caliber
Forum Newbie
Posts: 5
Joined: Sat Feb 08, 2003 1:14 pm

Get DB-date, ignore Ucase and Lcase

Post by Caliber »

I've got this query:

їcode]$query2 = pg_exec($conn, "
SELECT
*
FROM
mtrade_forum
WHERE
threadcontent LIKE '%$searchstring%'
OR
threadtopic LIKE '%$searchstring%'
ORDER BY
createddate DESC
");
ї/code]

The problem is that it only shows results that contains words that match EXACTLY what is in $searchstring. If I search for "Test" I only get records that contain "Test".
I want it to ignore UpperCase and LowerCase, so If I search for "Test" I'll get "test", "TEST", "tEsT" and so on.

How do I do that?
Thanx in advance.
Caliber
Forum Newbie
Posts: 5
Joined: Sat Feb 08, 2003 1:14 pm

Post by Caliber »

I'm using postgreSQL btw
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

Haven't used postgreSQL so I don't know if this will help. Might give you some ideas of what to look for.

In mysql, the BINARY attribute on a column specifies a case-sensitive match - leave it off for case insensitive.

Also (from the mysql manual):

"The BINARY attribute is sticky. This means that if a column marked BINARY is used in an expression, the whole expression is compared as a BINARY value. "
Rob the R
Forum Contributor
Posts: 128
Joined: Wed Nov 06, 2002 2:25 pm
Location: Houston

Post by Rob the R »

You could also use the strtoupper PHP function in combination with the upper PostgreSQL function to convert all the cases to upper-case when searching:

Code: Select all

$uppersearchstring = strtoupper($searchstring) ;
$query2 = pg_exec($conn, " 
                           SELECT 
                              * 
                           FROM 
                              mtrade_forum 
                           WHERE 
                              upper(threadcontent) LIKE '%$uppersearchstring%' 
                           OR 
                              upper(threadtopic) LIKE '%$uppersearchstring%' 
                           ORDER BY 
                              createddate DESC 
                        ");
This may slow things down if you have a large database to search through, since it would have to convert every record to upper-case to see if it matched.
Caliber
Forum Newbie
Posts: 5
Joined: Sat Feb 08, 2003 1:14 pm

Post by Caliber »

Thanx a lot for the help guys, it works now
Post Reply