Thread: using 'lower' in an sql statement?
using 'lower' in an sql statement?
9 Apr 2010 @ 16.22
i have the following code which i am using to try and pullout the records which include a string the user inputs.
for example if there is a record in the db which stores "this is my first string"
and the user searches for "this is my" then i want it to find it
and i have got this working but i run into trouble when the user inputs "This Is My" then it wont find the record because of the uppercase in the inputted string.
so i have tried this but that doesnt find a match for lower case input nor uppercase? $ sql = "SELECT * FROM info WHERE LOWER(data) LIKE LOWER('%" . $input . "%')" ;
anyone have any ideas
9 Apr 2010 @ 22.13
p.s the field type is a blob and lower doesnt work with it
so the doc says this but i am unable to incoroperate into my code
LOWER() (and UPPER()) are ineffective when applied to binary strings ( BINARY, VARBINARY, BLOB). To perform lettercase conversion, convert the string to a nonbinary string:
mysql> SET @str = BINARY 'New York';
mysql> SELECT LOWER(@str), LOWER(CONVERT(@str USING latin1));
| LOWER(@str) | LOWER(CONVERT(@str USING latin1)) |
| New York | new york |
anyone able to assist me with this?
10 Apr 2010 @ 18.00
Shot in the dark here, but do you have to use MySQL SET? What about something like this: Give that a shot.
$ sql = 'SELECT * FROM `info` WHERE LOWER(`data`) LIKE LOWER(CONVERT(\'' . mysql_real_escape_string ( $input ). '\' USING latin1));' ;
10 Apr 2010 @ 23.33
thanks for your reply,
unfortunatly i am unable to get this to work (thought i have been up the pub all day :D so that may have something to do with it!
i have changed your code slightly because its the data field which needs to be converted, unless your example converts the entire process? so i have this but this throws out a Incorrect parameters in the call to native function 'LOWER' $ sql = "SELECT * FROM info WHERE LOWER( $input ) LIKE LOWER(CONVERT(\''.mysql_real_escape_string(data).'\' USING latin1))" ;
your original code didnt throw out any errors but did not also find any data in the db
again thanks for your help with this, appreciate it.
10 Apr 2010 @ 23.54
Your edited code is slightly wrong. You are enclosing your SQL query in double quotes, and mine is enclosed in single quotes. You have to do some editing to make it would for double quotes: Give that a shot.
$ sql = "SELECT * FROM info WHERE LOWER( $input ) LIKE LOWER(CONVERT('" . mysql_real_escape_string ( data ). "' USING latin1))" ;
11 Apr 2010 @ 13.38
the above give the following error this 'might' be because the $input is the data the user enters so should that not be the one which i use mysql_real_escape_string on?
Notice: Use of undefined constant data - assumed 'data'
Unknown column 'empty' in 'where clause'
data is the db column
i tried this but that throws up $ sql = "SELECT * FROM info WHERE LOWER('" . mysql_real_escape_string ( $input ). "' LIKE LOWER(CONVERT(data) USING latin1))" ;
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') USING latin1)' at line 1
Last edited by ljackson; 11 Apr 2010 at @ 14.28.
By scottgm in forum PHP, ASP & Java
Last Post: 15 Dec 2010, @ 11.11
By ljackson in forum Computer Hardware & Software
Last Post: 24 Jul 2009, @ 23.03
By ljackson in forum Design & Layout
Last Post: 22 Jul 2009, @ 22.14 Tags for this Thread