Hello Everybody,

Does anyone know how to handle upper/lowercase data in database.

When I try to retrieve data from mysql database such as 'ict', it will not appear as a resulf if it is stored as 'ICT' and even Science if it is science.

Help is much appreciated
Many thanks.

    WHERE value LIKE 'ict'

    I do not know if there is anything better,
    but I think LIKE makes a case-insensitive search.
    But I could be wrong .....

      ok thanks,
      you are right. But I am doing boolean search. the code is like this..

      MySQL_select_db("coas5");
      if((!$all) || ($all == "")) { $all = ""; } else { $all = "+(".$all.")"; }
      if((!$POST['all'] ) || ($POST['all'] == "")) { $POST['all'] = ""; } else { $POST['all'] = "+(".$_POST['all'] .")"; }
      if((!$any) || ($any == "")) { $any = ""; }
      if((!$none) || ($none == "")) { $none = ""; } else { $none = "-(".$none.")"; }

      $query = " SELECT *,
      MATCH(subject, learningArea, noofstudents, minutes, ability) AGAINST ('$all $none $any' IN BOOLEAN MODE) AS score
      FROM lesson
      WHERE MATCH(subject, learningArea, noofstudents, minutes, ability) AGAINST ('$all $none $any' IN BOOLEAN MODE) ORDER BY score DESC " ;

      how can I edit this using LIKE syntax?

      Many thanks

        Which character collation are you using on the columns in question?

        Sounds like you just need to switch to a case insensitive collation (e.g. the "_ci" version of the collation you're using)

          bradgrafelman;10942619 wrote:

          Which character collation are you using on the columns in question?

          Sounds like you just need to switch to a case insensitive collation (e.g. the "_ci" version of the collation you're using)

          thanks, but I am not sure which one should I used. there is a list of choices.
          curently use utf18 general.
          language in the system- english
          any explanation?

          many thanks

            I'm guessing you meant utf8_general? If so, use utf8_general_ci.

              i chose ucs2' COLLATE 'ucs2_romanian_c by mistake. while trying to choose correct collation. nothing appears in my database now. what should I do.
              tq

                bradgrafelman;10942623 wrote:

                I'm guessing you meant utf8_general? If so, use utf8_general_ci.

                yes, I have used this but the output remains the same, nothing change.
                Anyone has this experience?
                help me please.
                thank you

                  bradgrafelman;10942623 wrote:

                  I'm guessing you meant utf8_general? If so, use utf8_general_ci.

                  It solved now, but just want to know- whether we should start creating a new database , table and fields as there is nothing to do with modification.

                  Should I change collation for the databse only or define them in every fields?
                  in the solve one, i created a new database with the suggsted collation, the fields as well

                  MAny thanks

                    bradgrafelman;10942623 wrote:

                    I'm guessing you meant utf8_general? If so, use utf8_general_ci.

                    It solved now, but just want to know- whether we should start creating a new database , table and fields as there is nothing to do with modification.

                    Should I change collation for the databse only or define them in every fields?
                    in the solve one, i created a new database with the suggsted collation, the fields as well

                    MAny thanks

                      No, you don't have to create a new database. You can change change collation down to column level. You can also specify collation in your order by clause

                      ORDER BY txt COLLATE some_valid_collation

                      And you can also select using a specific collation

                      SELECT txt COLLATE some_valid_collation
                        Write a Reply...