Re: Case sensitivity

[Johan De Meersman]

- Original Message -
 From: Reindl Harald h.rei...@thelounge.net
 Subject: Re: Case sensitivity
 
 ALTER TABLE `my_table` DEFAULT CHARACTER SET latin1 COLLATE
 latin1_general_ci;

Purely from memory, doesn't that change the table but add the old setting to 
individual text columns?

I seem to recall running into this.


-- 
Unhappiness is discouraged and will be corrected with kitten pictures.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Case sensitivity

[Hartmut Holzgraefe]

On 05/21/2014 08:14 AM, Johan De Meersman wrote:

 ALTER TABLE `my_table` DEFAULT CHARACTER SET latin1 COLLATE
 latin1_general_ci;
 
 Purely from memory, doesn't that change the table but add the old setting to 
 individual text columns?

right, the above will only change the default for new columns
added later, but existing columns will keep their current
character set and collation.

  ALTER TABLE tbl_name
CONVERT TO CHARACTER SET charset_name
[COLLATE collation_name]

is needed to change the default *and* all already existing
columns

-- 
Hartmut Holzgraefe, Principal Support Engineer (EMEA)
SkySQL - The MariaDB Company | http://www.skysql.com/

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Case sensitivity

[Reindl Harald]

Am 21.05.2014 10:50, schrieb Hartmut Holzgraefe:
 On 05/21/2014 08:14 AM, Johan De Meersman wrote:
 
 ALTER TABLE `my_table` DEFAULT CHARACTER SET latin1 COLLATE
 latin1_general_ci;

 Purely from memory, doesn't that change the table but add the old setting to 
 individual text columns?
 
 right, the above will only change the default for new columns
 added later, but existing columns will keep their current
 character set and collation.
 
   ALTER TABLE tbl_name
 CONVERT TO CHARACTER SET charset_name
 [COLLATE collation_name]
 
 is needed to change the default *and* all already existing columns

ah - i remember writing a script doing that for 5000 tables on the
whole infrastructure as charset/collation support started to get rid
of the ridicolous latin1_swedish_ci preset



signature.asc
Description: OpenPGP digital signature

Re: Case sensitivity

[Larry Martell]

On Wed, May 21, 2014 at 4:50 AM, Hartmut Holzgraefe hart...@skysql.com wrote:
 On 05/21/2014 08:14 AM, Johan De Meersman wrote:

 ALTER TABLE `my_table` DEFAULT CHARACTER SET latin1 COLLATE
 latin1_general_ci;

 Purely from memory, doesn't that change the table but add the old setting to 
 individual text columns?

 right, the above will only change the default for new columns
 added later, but existing columns will keep their current
 character set and collation.

   ALTER TABLE tbl_name
 CONVERT TO CHARACTER SET charset_name
 [COLLATE collation_name]

 is needed to change the default *and* all already existing
 columns

Thanks for all the help Hartmut.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Case sensitivity

[Reindl Harald]

Am 20.05.2014 15:26, schrieb Larry Martell:
 I have 2 servers, one is running 5.5.25a and the other 5.0.77.
 
 They both have the same collation and character set:
 
 mysql SELECT COLLATION(VERSION());
 +--+
 | COLLATION(VERSION()) |
 +--+
 | utf8_general_ci  |
 +--+
 1 row in set (0.00 sec)
 
 mysql show variables like '%_server';
 +--+--+
 | Variable_name| Value|
 +--+--+
 | character_set_server | utf8 |
 | collation_server | utf8_bin |
 +--+--+
 2 rows in set (0.00 sec)
 
 Yet the 5.5.25a server is case sensitive as you would expect, but the
 5.0.77 is not. How can I make the 5.0.77 server case sensitive?

and what collation have your tables and databases?
what collation is the client *really* using?

set collation_connection='utf8_general_ci';

what is in the servers environment variables and in the
cli-client don't say that much about applications and in
doubt your application should *always* set charset/collation



signature.asc
Description: OpenPGP digital signature

Re: Case sensitivity

[Hartmut Holzgraefe]

On 05/20/2014 03:26 PM, Larry Martell wrote:
 Yet the 5.5.25a server is case sensitive as you would expect, but the
 5.0.77 is not. How can I make the 5.0.77 server case sensitive?
 

please provide the output of

  SHOW VARIABLES LIKE 'character_set_%';

and

  SHOW VARIABLES LIKE 'collation_%';

and explain what exactly you mean by is case sensitive.
Are you referring to database and table names being case
sensitive here, or to comparisons (=, LIKE) on textual
values? Seeing a few example queries and the different
results returned by 5.0 and 5.5 would sure help, too ...

-- 
Hartmut Holzgraefe, Principal Support Engineer (EMEA)
SkySQL - The MariaDB Company | http://www.skysql.com/

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Case sensitivity

[Larry Martell]

On Tue, May 20, 2014 at 9:38 AM, Hartmut Holzgraefe hart...@skysql.com wrote:
 On 05/20/2014 03:26 PM, Larry Martell wrote:
 Yet the 5.5.25a server is case sensitive as you would expect, but the
 5.0.77 is not. How can I make the 5.0.77 server case sensitive?


 please provide the output of

   SHOW VARIABLES LIKE 'character_set_%';

 and

   SHOW VARIABLES LIKE 'collation_%';

Here it is from the server that is case sensitive:

mysql show variables like 'character_set_%';
+--++
| Variable_name| Value  |
+--++
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database   | utf8   |
| character_set_filesystem | binary |
| character_set_results| latin1 |
 | character_set_server | utf8   |
| character_set_system | utf8   |
 | character_sets_dir   | /export/home/mysql/share/charsets/
| +--++
8 rows in set (0.00 sec)

mysql show variables like 'collation_%';
+--+---+
| Variable_name| Value |
+--+---+
| collation_connection | latin1_swedish_ci |
| collation_database   | utf8_bin  |
| collation_server | utf8_bin  |
+--+---+
3 rows in set (0.00 sec)

And here is it from the server that is not case sensitive:

mysql show variables like 'character_set_%';
+--++
| Variable_name| Value  |
+--++
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database   | latin1 |
| character_set_filesystem | binary |
| character_set_results| latin1 |
| character_set_server | utf8   |
| character_set_system | utf8   |
| character_sets_dir   | /usr/share/mysql/charsets/ |
+--++
8 rows in set (0.00 sec)

mysql show variables like 'collation_%';
+--+---+
| Variable_name| Value |
+--+---+
| collation_connection | latin1_swedish_ci |
| collation_database   | latin1_swedish_ci |
| collation_server | utf8_bin  |
+--+---+
3 rows in set (0.00 sec)

Both servers have this in their config files, with no other character
set or collation settings:

character_set_server=utf8
collation_server=utf8_bin

Clearly they're different, but I don't know how to get them to be the same.

 and explain what exactly you mean by is case sensitive.
 Are you referring to database and table names being case
 sensitive here, or to comparisons (=, LIKE) on textual
 values?

I mean when querying data.


 Seeing a few example queries and the different
 results returned by 5.0 and 5.5 would sure help, too ...

5.5 server:

mysql select distinct(pnlCurrency) from PnLDetail;
+-+
| pnlCurrency |
+-+
| USD |
| USd |
+-+
2 rows in set (0.00 sec)

5.0 server:

mysql select distinct(pnlCurrency) from PnLDetail;
+-+
| pnlCurrency |
+-+
| USD |
+-+
1 row in set (0.01 sec)

Both have the same data in them.

Thanks!

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Case sensitivity

[Hartmut Holzgraefe]

On 05/20/2014 11:10 PM, Larry Martell wrote:

 5.5 server:
 
 mysql select distinct(pnlCurrency) from PnLDetail;
 +-+
 | pnlCurrency |
 +-+
 | USD |
 | USd |
 +-+
 2 rows in set (0.00 sec)
 
 5.0 server:
 
 mysql select distinct(pnlCurrency) from PnLDetail;
 +-+
 | pnlCurrency |
 +-+
 | USD |
 +-+
 1 row in set (0.01 sec)
 
 Both have the same data in them.

ok, in this case the interesting piece of information
would be the SHOW CREATE TABLE pnlCurrency; output
from both.

As the collation_database variables differ between the
two servers (utf8_bin vs. latin1_swedish_ci) I assume
that the same is the case for the actual tables, and that
would perfectly explain why one treads lower/upper case
as distinct while the other doesn't


-- 
Hartmut Holzgraefe, Principal Support Engineer (EMEA)
SkySQL - The MariaDB Company | http://www.skysql.com/

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Case sensitivity

[Larry Martell]

On Tue, May 20, 2014 at 5:52 PM, Hartmut Holzgraefe hart...@skysql.com wrote:
 On 05/20/2014 11:10 PM, Larry Martell wrote:

 5.5 server:

 mysql select distinct(pnlCurrency) from PnLDetail;
 +-+
 | pnlCurrency |
 +-+
 | USD |
 | USd |
 +-+
 2 rows in set (0.00 sec)

 5.0 server:

 mysql select distinct(pnlCurrency) from PnLDetail;
 +-+
 | pnlCurrency |
 +-+
 | USD |
 +-+
 1 row in set (0.01 sec)

 Both have the same data in them.

 ok, in this case the interesting piece of information
 would be the SHOW CREATE TABLE pnlCurrency; output
 from both.

 As the collation_database variables differ between the
 two servers (utf8_bin vs. latin1_swedish_ci) I assume
 that the same is the case for the actual tables, and that
 would perfectly explain why one treads lower/upper case
 as distinct while the other doesn't

The create table on 5.5 has:

DEFAULT CHARSET=uft8
COLLATE=uft8_bin

and on the 5.0 server it has:

DEFAULT CHARSET=latin1

But the tables on both servers were created from DDL files that do not
specify either one of these. So are these inherited from the server
settings at the time the tables are created? how do I fix this?
Specify DEFAULT CHARSET and COLLATE on the CREATE TABLE statements in
the DDL file and drop and recreate all the tables? Is there a way fo
change this without doing that? Can the collation_database variables
be set in the conf file? I didn't see them mentioned in the docs.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Case sensitivity

[Reindl Harald]

Am 21.05.2014 00:48, schrieb Larry Martell:
 ok, in this case the interesting piece of information
 would be the SHOW CREATE TABLE pnlCurrency; output
 from both.

 As the collation_database variables differ between the
 two servers (utf8_bin vs. latin1_swedish_ci) I assume
 that the same is the case for the actual tables, and that
 would perfectly explain why one treads lower/upper case
 as distinct while the other doesn't
 
 The create table on 5.5 has:
 
 DEFAULT CHARSET=uft8
 COLLATE=uft8_bin
 
 and on the 5.0 server it has:
 
 DEFAULT CHARSET=latin1
 
 But the tables on both servers were created from DDL files that do not
 specify either one of these. So are these inherited from the server
 settings at the time the tables are created? how do I fix this?

they are inherited from the *database*

get phpMyAdmin, you can learn a lot of it outputs and
the generated queries of most actions

 Specify DEFAULT CHARSET and COLLATE on the CREATE TABLE statements in
 the DDL file and drop and recreate all the tables? Is there a way fo
 change this without doing that? Can the collation_database variables
 be set in the conf file? I didn't see them mentioned in the docs

DDL files which don't specify charset / collation are broken because
whoever writes them has no idea of the default settings

surely: http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

ALTER TABLE `my_table` DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci;





signature.asc
Description: OpenPGP digital signature

Re: Case sensitivity problem (I think) on OS X

[Paul DuBois]

At 15:22 -0600 3/16/06, Sterling Anderson wrote:
I'm running MySQL 5.0.18 on OS X 10.4.5, on an intel MacBook. My 
table definition is:


CREATE TABLE `UNIT` (
  `UNIT_ID` int(11) NOT NULL auto_increment,
  `UNIT_CODE` varchar(50) default NULL,
  `UNIT_DESC` varchar(50) default NULL,
  `USER_ID` int(11) NOT NULL,
  `USER_DT` datetime NOT NULL,
  `RESEARCH_SITE_ID` int(11) NOT NULL,
  PRIMARY KEY  (`UNIT_ID`),
  UNIQUE KEY `UNIT_CODE` (`UNIT_CODE`),
  KEY `RESEARCH_SITE_ID` (`RESEARCH_SITE_ID`),
  KEY `USER_ID` (`USER_ID`),
  CONSTRAINT `unit_ibfk_2` FOREIGN KEY (`USER_ID`)
REFERENCES `users` (`USER_ID`),
  CONSTRAINT `unit_ibfk_1` FOREIGN KEY (`RESEARCH_SITE_ID`)
REFERENCES `research_site` (`RESEARCH_SITE_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

When I try inserting I am getting an error relating to the 
`UNIT_CODE` UNIQUE KEY. See below:


mysql INSERT INTO UNIT(UNIT_ID, UNIT_CODE, UNIT_DESC, USER_ID, 
USER_DT, RESEARCH_SITE_ID)

-   VALUES(9, 'g/dL', NULL, 1, '2005-03-22 08:01:07 AM', 0);
Query OK, 1 row affected, 1 warning (0.00 sec)

This works fine.

mysql INSERT INTO UNIT(UNIT_ID, UNIT_CODE, UNIT_DESC, USER_ID, 
USER_DT, RESEARCH_SITE_ID)

-   VALUES(10, 'g/dl', NULL, 1, '2005-03-22 08:01:07 AM', 0);
ERROR 1062 (23000): Duplicate entry 'g/dl' for key 2

This fails however. 'g/dl' != 'g/dL' though. I don't get why a 
string field is being treated this way. I understand the HFS+ 
filesystem is not case sensitive but that shouldn't be an issued 
with varchar fields should it?


No, it's not a filesystem issue.  It's a character set issue.  The column
has a character set of utf8, and the default collation (which determines
the comparison and sorting characteristics is utf8_general_ci:

mysql show collation like 'utf8%';
++-+-+-+--+-+
| Collation  | Charset | Id  | Default | Compiled | Sortlen |
++-+-+-+--+-+
| utf8_general_ci| utf8|  33 | Yes | Yes  |   1 |
| utf8_bin   | utf8|  83 | | Yes  |   1 |
| utf8_unicode_ci| utf8| 192 | | Yes  |   8 |
| utf8_icelandic_ci  | utf8| 193 | | Yes  |   8 |
| utf8_latvian_ci| utf8| 194 | | Yes  |   8 |
| utf8_romanian_ci   | utf8| 195 | | Yes  |   8 |
| utf8_slovenian_ci  | utf8| 196 | | Yes  |   8 |
| utf8_polish_ci | utf8| 197 | | Yes  |   8 |
| utf8_estonian_ci   | utf8| 198 | | Yes  |   8 |
| utf8_spanish_ci| utf8| 199 | | Yes  |   8 |
| utf8_swedish_ci| utf8| 200 | | Yes  |   8 |
| utf8_turkish_ci| utf8| 201 | | Yes  |   8 |
| utf8_czech_ci  | utf8| 202 | | Yes  |   8 |
| utf8_danish_ci | utf8| 203 | | Yes  |   8 |
| utf8_lithuanian_ci | utf8| 204 | | Yes  |   8 |
| utf8_slovak_ci | utf8| 205 | | Yes  |   8 |
| utf8_spanish2_ci   | utf8| 206 | | Yes  |   8 |
| utf8_roman_ci  | utf8| 207 | | Yes  |   8 |
| utf8_persian_ci| utf8| 208 | | Yes  |   8 |
| utf8_esperanto_ci  | utf8| 209 | | Yes  |   8 |
| utf8_hungarian_ci  | utf8| 210 | | Yes  |   8 |
++-+-+-+--+-+


ci means case insensitive and that's why you're seeing the results
that you do.  You could use the utf8_bin collation for this column
if you want comparisons to be based on binary character values (which
will, in effect, give you case sensitive behavior).

--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Case sensitivity problem (I think) on OS X

[Sterling Anderson]

On Mar 16, 2006, at 3:49 PM, Paul DuBois wrote:

This fails however. 'g/dl' != 'g/dL' though. I don't get why a  
string field is being treated this way. I understand the HFS+  
filesystem is not case sensitive but that shouldn't be an issued  
with varchar fields should it?


No, it's not a filesystem issue.  It's a character set issue.  The  
column
has a character set of utf8, and the default collation (which  
determines

the comparison and sorting characteristics is utf8_general_ci:

mysql show collation like 'utf8%';
++-+-+-+--+-+
| Collation  | Charset | Id  | Default | Compiled | Sortlen |
++-+-+-+--+-+
| utf8_general_ci| utf8|  33 | Yes | Yes  |   1 |
| utf8_bin   | utf8|  83 | | Yes  |   1 |


...



ci means case insensitive and that's why you're seeing the results
that you do.  You could use the utf8_bin collation for this column
if you want comparisons to be based on binary character values (which
will, in effect, give you case sensitive behavior).



So, it sounds like it may be more logical for me to use utf8_bin  
universally or perhaps a different character set like latin1? I'm  
curious because we contracted someone at DoIT (the UW) to migrate an  
Oracle DB to MySQL, and they chose to use utf8. Now I'm having this  
problem.



I almost contacted you directly. Not sure if you recognize my name,  
I'm with WNPRC.


--
Sterling Anderson
email/aim: sterling_anderson [at] mac.com
http://sterlinganderson.net/
608.577.7208



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Case sensitivity problem (I think) on OS X

[Paul DuBois]

At 16:01 -0600 3/16/06, Sterling Anderson wrote:

On Mar 16, 2006, at 3:49 PM, Paul DuBois wrote:

This fails however. 'g/dl' != 'g/dL' though. I don't get why a 
string field is being treated this way. I understand the HFS+ 
filesystem is not case sensitive but that shouldn't be an issued 
with varchar fields should it?


No, it's not a filesystem issue.  It's a character set issue.  The column
has a character set of utf8, and the default collation (which determines
the comparison and sorting characteristics is utf8_general_ci:

mysql show collation like 'utf8%';
++-+-+-+--+-+
| Collation  | Charset | Id  | Default | Compiled | Sortlen |
++-+-+-+--+-+
| utf8_general_ci| utf8|  33 | Yes | Yes  |   1 |
| utf8_bin   | utf8|  83 | | Yes  |   1 |


...



ci means case insensitive and that's why you're seeing the results
that you do.  You could use the utf8_bin collation for this column
if you want comparisons to be based on binary character values (which
will, in effect, give you case sensitive behavior).



So, it sounds like it may be more logical for me to use utf8_bin 
universally or perhaps a different character set like latin1? I'm 
curious because we contracted someone at DoIT (the UW) to migrate an 
Oracle DB to MySQL, and they chose to use utf8. Now I'm having this 
problem.


The character set is not the issue.  The collation is.  That's what
determines the comparison and sorting properties.

DoIT's choice of utf8 is neutral, and it makes sense that they chose
to use the default case-insensitive collation unless you specified in
advance that you require case sensitive comparisons.

Changing to latin1 won't in itself make any difference because its default
collation is case insensitive, too.  You do have the choice of using an
explicitly case-sensitive collation, though: latin1_general_cs


--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: case sensitivity

[Brent Baisley]

Instead of using COLLATE you can try using the BINARY option  
instead. It will work in older versions of MySQL and I think it makes  
you select a bit more readable. Just put BINARY before the  
comparison you want to be case sensitive.


WHERE BINARY text LIKE '%Jehovah%'

On Nov 8, 2005, at 9:15 AM, Scott Hamm wrote:


I am running MySQL 5.0.15-nt on Windows 2000 PRO and use PHP as
front-end for word search in Bible.  My goal is to set an option for
case sensitivity in text search.  For example, if I want to search for
the word Jehovah* I would expect Jehovah not JEHOVAH.  This
query works for my objective:

SELECT
bo.book,
b.chapter,
b.verse,
b.text
FROM
avkjv.books bo
LEFT JOIN
avkjv.bible b ON b.book=bo.number
WHERE
text
COLLATE
latin1_general_cs
LIKE
CONCAT('%','Jehovah','%');

But if I want to turn case sensitivity off I tried to issue the query:

SELECT
bo.book,
b.chapter,
b.verse,
b.text
FROM
avkjv.books bo
LEFT JOIN
avkjv.bible b
ON
b.book=bo.number
WHERE
text
LIKE
CONCAT('%','Jehovah','%');

Without COLLATE function, and the result acted as if it is still case
sensitive.  I tried to look up in mysql to no avail.  How can I turn
'case sensitivity' off?


Thanks in advance,


Scott

--
Please avoid sending me Word or PowerPoint attachments.
See http://www.gnu.org/philosophy/no-word-attachments.html

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql? 
[EMAIL PROTECTED]






--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search  Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: case sensitivity

[Scott Hamm]

Can this also be used? (just now figured out a way)

   SELECT
   bo.book,
   b.chapter,
   b.verse,
   b.text
   FROM
   avkjv.books bo
   LEFT JOIN
   avkjv.bible b
   ON
   b.book=bo.number
   WHERE
   LCASE(text)
   LIKE
   CONCAT('%',LCASE('$srch'),'%');
On 11/8/05, Brent Baisley [EMAIL PROTECTED] wrote:
 Instead of using COLLATE you can try using the BINARY option
 instead. It will work in older versions of MySQL and I think it makes
 you select a bit more readable. Just put BINARY before the
 comparison you want to be case sensitive.

 WHERE BINARY text LIKE '%Jehovah%'

 On Nov 8, 2005, at 9:15 AM, Scott Hamm wrote:

  I am running MySQL 5.0.15-nt on Windows 2000 PRO and use PHP as
  front-end for word search in Bible.  My goal is to set an option for
  case sensitivity in text search.  For example, if I want to search for
  the word Jehovah* I would expect Jehovah not JEHOVAH.  This
  query works for my objective:
 
  SELECT
  bo.book,
  b.chapter,
  b.verse,
  b.text
  FROM
  avkjv.books bo
  LEFT JOIN
  avkjv.bible b ON b.book=bo.number
  WHERE
  text
  COLLATE
  latin1_general_cs
  LIKE
  CONCAT('%','Jehovah','%');
 
  But if I want to turn case sensitivity off I tried to issue the query:
 
  SELECT
  bo.book,
  b.chapter,
  b.verse,
  b.text
  FROM
  avkjv.books bo
  LEFT JOIN
  avkjv.bible b
  ON
  b.book=bo.number
  WHERE
  text
  LIKE
  CONCAT('%','Jehovah','%');
 
  Without COLLATE function, and the result acted as if it is still case
  sensitive.  I tried to look up in mysql to no avail.  How can I turn
  'case sensitivity' off?
 
 
  Thanks in advance,
 
 
  Scott
 
  --
  Please avoid sending me Word or PowerPoint attachments.
  See http://www.gnu.org/philosophy/no-word-attachments.html
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/mysql?
  [EMAIL PROTECTED]
 
 
 

 --
 Brent Baisley
 Systems Architect
 Landover Associates, Inc.
 Search  Advisory Services for Advanced Technology Environments
 p: 212.759.6400/800.759.0577





--
Please avoid sending me Word or PowerPoint attachments.
See http://www.gnu.org/philosophy/no-word-attachments.html

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: case sensitivity of table names

[Paul DuBois]

At 12:22 -0500 12/17/03, Mayuran Yogarajah wrote:
I am using case sensitive table names when I create tables
like :
CREATE TABLE MyTest;

If I want to do a select from this table, I have to do
SELECT * FROM MyTest, not SELECT * FROM mytest.
How can I make it so that the table name is still MyTest
but selects work with mytest ?
You can set the lower_case_table_names server variable to 1.
Then table names will not be treated as case sensitive,
and you can write them in any lettercase in your queries.
Two points to note, though:

- Before setting the varable, rename all your tables to lowercase.
Otherwise they won't be recognized properly when you set the variable.
(The way it works is that if causes the server to lowercase the names
of new tables when they are created.)
- Although you will be able to refer to tables using any lettercase,
*within a given query*, you must refer to the table consistently.
See http://www.mysql.com/doc/en/Name_case_sensitivity.html for
an example.
--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified?  http://www.mysql.com/certification/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: case sensitivity of table names

[Mayuran Yogarajah]

Paul DuBois wrote:

At 12:22 -0500 12/17/03, Mayuran Yogarajah wrote:

I am using case sensitive table names when I create tables
like :
CREATE TABLE MyTest;

If I want to do a select from this table, I have to do
SELECT * FROM MyTest, not SELECT * FROM mytest.
How can I make it so that the table name is still MyTest
but selects work with mytest ?


You can set the lower_case_table_names server variable to 1.
Then table names will not be treated as case sensitive,
and you can write them in any lettercase in your queries.
Two points to note, though:

- Before setting the varable, rename all your tables to lowercase.
Otherwise they won't be recognized properly when you set the variable.
(The way it works is that if causes the server to lowercase the names
of new tables when they are created.)
- Although you will be able to refer to tables using any lettercase,
*within a given query*, you must refer to the table consistently.
See http://www.mysql.com/doc/en/Name_case_sensitivity.html for
an example.

Is it possible to change the variable lower_case_table_names from mysql 
commandline?
I tried to change it by doing this :
mysql SET lower_case_table_names=1;

and got the error :
ERROR 1193: Unknown system variable 'lower_case_table_names'
Thanks



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: case sensitivity of table names

[Paul DuBois]

At 13:00 -0500 12/17/03, Mayuran Yogarajah wrote:
Paul DuBois wrote:

At 12:22 -0500 12/17/03, Mayuran Yogarajah wrote:

I am using case sensitive table names when I create tables
like :
CREATE TABLE MyTest;

If I want to do a select from this table, I have to do
SELECT * FROM MyTest, not SELECT * FROM mytest.
How can I make it so that the table name is still MyTest
but selects work with mytest ?


You can set the lower_case_table_names server variable to 1.
Then table names will not be treated as case sensitive,
and you can write them in any lettercase in your queries.
Two points to note, though:

- Before setting the varable, rename all your tables to lowercase.
Otherwise they won't be recognized properly when you set the variable.
(The way it works is that if causes the server to lowercase the names
of new tables when they are created.)
- Although you will be able to refer to tables using any lettercase,
*within a given query*, you must refer to the table consistently.
See http://www.mysql.com/doc/en/Name_case_sensitivity.html for
an example.
Is it possible to change the variable lower_case_table_names from 
mysql commandline?
I tried to change it by doing this :
mysql SET lower_case_table_names=1;

and got the error :
ERROR 1193: Unknown system variable 'lower_case_table_names'
No, it must be set at startup time.  Besides, if you set it with a
SET statement, the value would be lost the next time you start the
server.
Best to put it in an option file so that it's used each time the
server starts up:
[mysqld]
lower_case_table_names=1
--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified?  http://www.mysql.com/certification/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: case sensitivity in a delete query

[Paul DuBois]

At 17:51 -0500 3/2/03, Sunfire wrote:
hi i have a delete query that will delete a record based on the company
name:
delete from members where company='$company';
the query works fine and all but there is a problem because if there is more
than 1 company listed but with different use of case in their name then
allrecords that match $company are deleted.. so:
BLACK
black
Black
BlacK and so on are all considered the same entry by delete and will wipe
them all even if all i wanted to do was to delete BLACK..
so was just wondering how to fix that if it is even possible.. i fear that
the same problem will happen with my update statements also and want them
changed too because if BLACK needs to be updated because the phone number
changes then i dont need BlacK to inherrit the same phone number
anybody know how to fix?
If your column is CHAR, VARCHAR, or TEXT, use CHAR BINARY, VARCHAR BINARY,
or BLOB instead.
Or just change the query to ... where company = BINARY '$company'.

Comparisons of binary strings are case sensitive.

Best to read the sections in the manual that describe the string columns
to get an idea of how they work.  It'll help you avoid problems like this
in the future.
-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Case sensitivity

[Mike Grabski]

You have two choices, you can use a binary datatype for the field, or force
a binary comparison using the BINARY operator.

http://www.mysql.com/doc/en/Case_Sensitivity_Operators.html
http://www.mysql.com/doc/en/Column_types.html


query, sql

Mike



-Original Message-
From: asp52 [mailto:[EMAIL PROTECTED]]
Sent: Thursday, December 19, 2002 9:06 AM
To: [EMAIL PROTECTED]
Subject: Case sensitivity


Hi,
I have been searching tables based on search values which turn up tobe fine,
however the results dont reflect case sensitivity.

what i mean,

if i run following query

select field1 from table1 where name='text1';

this works fine but returns the same result set with text1 values of 'USER'
or 'user'.

i need to run the query which should run case sensitive. ie USER is
different from user

Thanks in advance who can throw some ligth

Adamji





-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Case sensitivity

[asp52]

Thanks,
Just worked it out with binary statement in my query.
all fine.
Would it be wise to use binary type table?
didnt try that as i wanted a flexible table of type varchar to perserve
space.
what would be its impact?
cheers.
Adamji



From: Mike Grabski [EMAIL PROTECTED]
To: 'asp52' [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Thursday, December 19, 2002 2:29 PM
Subject: RE: Case sensitivity


 You have two choices, you can use a binary datatype for the field, or
force
 a binary comparison using the BINARY operator.

 http://www.mysql.com/doc/en/Case_Sensitivity_Operators.html
 http://www.mysql.com/doc/en/Column_types.html


 query, sql

 Mike



 -Original Message-
 From: asp52 [mailto:[EMAIL PROTECTED]]
 Sent: Thursday, December 19, 2002 9:06 AM
 To: [EMAIL PROTECTED]
 Subject: Case sensitivity


 Hi,
 I have been searching tables based on search values which turn up tobe
fine,
 however the results dont reflect case sensitivity.

 what i mean,

 if i run following query

 select field1 from table1 where name='text1';

 this works fine but returns the same result set with text1 values of
'USER'
 or 'user'.

 i need to run the query which should run case sensitive. ie USER is
 different from user

 Thanks in advance who can throw some ligth

 Adamji





 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)

 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail
 [EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Case sensitivity

[Alec . Cawley]

See http://www.mysql.com/doc/en/CHAR.html or
http://www.mysql.com/doc/en/BLOB.html .

If your column types are CHAR or VARCHAR insert BINARY.

If your column type is TEXT, change to BLOB.


--
You said


Hi,
I have been searching tables based on search values which turn up tobe
fine,
however the results dont reflect case sensitivity.

what i mean,

if i run following query

select field1 from table1 where name='text1';

this works fine but returns the same result set with text1 values of 'USER'
or 'user'.

i need to run the query which should run case sensitive. ie USER is
different from user

Thanks in advance who can throw some ligth

Adamji



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Case sensitivity

[Mike Grabski]

You can specify a varchar field as binary, for instance: field1 varchar(50)
binary

query, sql

Mike

-Original Message-
From: asp52 [mailto:[EMAIL PROTECTED]]
Sent: Thursday, December 19, 2002 9:50 AM
To: Mike Grabski; [EMAIL PROTECTED]
Subject: Re: Case sensitivity



Thanks,
Just worked it out with binary statement in my query.
all fine.
Would it be wise to use binary type table?
didnt try that as i wanted a flexible table of type varchar to perserve
space.
what would be its impact?
cheers.
Adamji



From: Mike Grabski [EMAIL PROTECTED]
To: 'asp52' [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Thursday, December 19, 2002 2:29 PM
Subject: RE: Case sensitivity


 You have two choices, you can use a binary datatype for the field, or
force
 a binary comparison using the BINARY operator.

 http://www.mysql.com/doc/en/Case_Sensitivity_Operators.html
 http://www.mysql.com/doc/en/Column_types.html


 query, sql

 Mike



 -Original Message-
 From: asp52 [mailto:[EMAIL PROTECTED]]
 Sent: Thursday, December 19, 2002 9:06 AM
 To: [EMAIL PROTECTED]
 Subject: Case sensitivity


 Hi,
 I have been searching tables based on search values which turn up tobe
fine,
 however the results dont reflect case sensitivity.

 what i mean,

 if i run following query

 select field1 from table1 where name='text1';

 this works fine but returns the same result set with text1 values of
'USER'
 or 'user'.

 i need to run the query which should run case sensitive. ie USER is
 different from user

 Thanks in advance who can throw some ligth

 Adamji





 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)

 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail
 [EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Case Sensitivity for CHECK TABLE TABLENAME??

[Gerald R. Jensen]

Mark:

Do you have my.ini (or my.cfg) set to ignore case in table names?
set-variable = lower_case_table_names=1

Gerald Jensen

- Original Message -
From: Mark Weiler [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, June 24, 2002 5:56 PM
Subject: Case Sensitivity for CHECK TABLE TABLENAME??


Mac OS X
mysql 3.23.47

I have a database with a table named: tname.

After several inserts, I run
CHECK TABLE tname;

No errors are reported.

I then run

CHECK TABLE TNAME;

And errors are reported!

Which is it? Errors or no errors?


Thanks

Mark


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php






-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: case-sensitivity broken in FK clause for MySQL+InnoDB

[Heikki Tuuri]

Hi!

The fact that foreign key definitions do not honor lower_case_table_names=1
is mentioned in the manual. I have added the fix to this on the TODO list
for July 2002.

You have found the right place, this mailing list is the right place to
report bugs in InnoDB.

Best regards,

Heikki Tuuri
Innobase Oy
---
InnoDB - transactions, hot backup, and foreign key support for MySQL
See http://www.innodb.com, download MySQL-Max from http://www.mysql.com

- Original Message -
From: [EMAIL PROTECTED]
Newsgroups: mailing.database.mysql
Sent: Wednesday, May 22, 2002 9:52 PM
Subject: case-sensitivity broken in FK clause for MySQL+InnoDB


 Description:
 When using MySQL with InnoDB table type, and turning on the
 table case-insensitivity flag in the config file
 (set-variable = lower_case_table_names=1), MySQL/InnoDB does not
 correctly handle the case-insensitivity of the target table name in
 the REFERENCES part of the FOREIGN KEY clause.  This is true for
 both the CREATE TABLE and ALTER TABLE variations.  Note the
 column names and the referer table name (in the case of the ALTER
 TABLE clause) are correctly treated as case insensitive.

 This problem may need to be posted to InnoDB, but I couldn't
 locate any method of notifying them of bug reports.

 Example script which shows the cases that work and the one that
 doesn't is attached in the How-To-Repeat.
 precise description of the problem (multiple lines)
 How-To-Repeat:
 drop table if exists foo1;
 create table foo1(
 pk1 integer NOT NULL,
 primary key(pk1)
 ) type = innodb;


 ##
 ## This will work fine
 ##
 drop table if exists foo2;
 create table foo2(
 pk1 integer NOT NULL,
 fk1 integer,
 foreign key (fk1) references foo1 (pk1),
 index ix1 (fk1)
 ) type = innodb;


 ##
 ## As will this
 ##
 drop table if exists foo2;
 create table foo2(
 pk1 integer NOT NULL,
 fk1 integer,
 foreign key (FK1) references foo1 (PK1),
 index ix1 (fk1)
 ) type = innodb;


 ##
 ## But this won't
 ## In my case, the specific error was:
 ##ERROR 1005: Can't create table './dbname/foo2.frm' (errno: 150)
 ##
 drop table if exists foo2;
 create table foo2(
 pk1 integer NOT NULL,
 fk1 integer,
 foreign key (fk1) references FOO1 (pk1),
 index ix1 (fk1)
 ) type = innodb;


 Fix:
 how to correct or work around the problem, if known (multiple lines)

 Submitter-Id: submitter ID
 Originator: Eugene Bekker
 Organization:
  organization of PR author (multiple lines)
 MySQL support: none
 Synopsis: case-sensitivity broken in FK clause for MySQL+InnoDB
 Severity: serious
 Priority: medium
 Category: mysql
 Class: sw-bug
 Release: mysql-3.23.50 (Official MySQL RPM)

 Environment:
 RedHat 7.1
 Using pre-packaged 3.23.50 (Max) from www.mysql.com
 machine, os, target, libraries (multiple lines)
 System: Linux betelgeuse.powervision.com 2.4.2-2 #1 Sun Apr 8 20:41:30 EDT
2001 i686 unknown
 Architecture: i686

 Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc
/usr/bin/cc
 GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs
 gcc version 2.96 2731 (Red Hat Linux 7.1 2.96-81)
 Compilation info: CC='gcc'
 CFLAGS='-O6 -fno-omit-frame-pointer -mcpu=pentiumpro'  CXX='gcc'
CXXFLAGS='-O6 -fno-omit-frame-pointer   -felide-constructors -fno-ex
ceptions -fno-rtti -mcpu=pentiumpro'  LDFLAGS=''
 LIBC:
 lrwxrwxrwx1 root root   13 Feb  2 07:17 /lib/libc.so.6 -
libc-2.2.2.so
 -rwxr-xr-x1 root root  1236396 Apr  6  2001 /lib/libc-2.2.2.so
 -rw-r--r--1 root root 26350254 Apr  6  2001 /usr/lib/libc.a
 -rw-r--r--1 root root  178 Apr  6  2001 /usr/lib/libc.so
 Configure command:
./configure  --disable-shared --with-mysqld-ldflags=-all-static --with-clien
t-ldflags=-all-static --with-other-libc=/usr/local/mysql-glibc --without-ber
keley-db --without-innodb --enable-assembler --enable-local-infile --with-my
sqld-user=mysql --with-unix-socket-path=/var/lib/mysql/mysql.sock --prefix=/
 --with-extra-charsets=complex --exec-prefix=/usr --libexecdir=/usr/sbin --s
ysconfdir=/etc --datadir=/usr/share --localstatedir=/var/lib/mysql --infodir
=/usr/info --includedir=/usr/include --mandir=/usr/man
'--with-comment=Official MySQL RPM'



 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)

 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail 

Re: Case sensitivity in Mysql

[BD]

At 02:05 AM 3/14/2002, you wrote:
Hi,
I run mysql on windows as well as linux.
table creation and access right are given  by  java program.

Problem im facing is.,
i create a table with name TEMP
in windows in creates table with name temp and linux TEMP
while giving rights thru GRANTstatement , it fails in windows cause it
expect TEMP not  temp

How can i create  table name with upper case  in Windows

create table TEMP ( x varchar(6),  y varchar(6))
this creates  a table with name temp..not TEMP


help me with mysql

Anis,
 Talking from experience, it is best to create all database names 
and table names in lower case. It will prevent a lot of grey hairs in the 
future.
 You can have upper and lower case column names, but I also put 
them in lower case to avoid mistyping the case of a letter. If you keep 
everything in lowercase, life will be much easier.

Brent

Why or why couldn't MySQL have used case insensitive names in the first 
place? Store everything in lower case internally, but allow the user to use 
Invoice_Number or invoice_number or INVOICE_NUMBER in the SQL 
statement and it should refer to the same column. Same with table names. I 
don't see why you would want to have 3 different columns in the same table 
with names Invoice_Number, invoice_number and INVOICE_NUMBER. Maybe 
it's just me, but it doesn't make a lot of sense to have case insensitive 
names. It just leads to typos and the code doesn't run. :-(


_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Case sensitivity in Mysql

[Christopher Thompson]

On Thursday 14 March 2002 9:32 am, BD wrote:

 Why or why couldn't MySQL have used case insensitive names in the first
 place? Store everything in lower case internally, but allow the user to use
 Invoice_Number or invoice_number or INVOICE_NUMBER in the SQL
 statement and it should refer to the same column. Same with table names. I
 don't see why you would want to have 3 different columns in the same table
 with names Invoice_Number, invoice_number and INVOICE_NUMBER. Maybe
 it's just me, but it doesn't make a lot of sense to have case insensitive
 names. It just leads to typos and the code doesn't run. :-(

In SQL92, identifiers can only be uppercase letters and numerals.  Therefore, 
if you are using lowercase, you are NOT writing valid SQL92.  Most databases 
will allow this, of course.  SQL99 adds support for lowercase characters but 
MySQL doesn't claim to implement SQL99 yet.

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Case sensitivity in Mysql

[BD]

At 11:17 AM 3/14/2002, you wrote:
On Thursday 14 March 2002 9:32 am, BD wrote:
 
  Why or why couldn't MySQL have used case insensitive names in the first
  place? Store everything in lower case internally, but allow the user to use
  Invoice_Number or invoice_number or INVOICE_NUMBER in the SQL
  statement and it should refer to the same column. Same with table names. I
  don't see why you would want to have 3 different columns in the same table
  with names Invoice_Number, invoice_number and INVOICE_NUMBER. Maybe
  it's just me, but it doesn't make a lot of sense to have case insensitive
  names. It just leads to typos and the code doesn't run. :-(

In SQL92, identifiers can only be uppercase letters and numerals.  Therefore,
if you are using lowercase, you are NOT writing valid SQL92.  Most databases
will allow this, of course.  SQL99 adds support for lowercase characters but
MySQL doesn't claim to implement SQL99 yet.

I don't mind switching to uppercase entities if it means I won't have to be 
on the lookout for the SQL police. The next knock on the door could be SQL 
police coming to confiscate my database or levy heavy fines. :-)  (Don't 
laugh. I don't know if you know this or not, but in Quebec there are 
language laws preventing store owners from having English only signs or 
signs where the English text is larger than the French text.  The English 
text must be smaller than the French text otherwise the language police 
will come knocking on your door and will force you to take the sign down or 
fine you. The people who refuse end up in court.  I hope the ANSI standards 
committee isn't listening because I could be in big trouble!)

Seriously though, you're right, the table and column names should be in 
uppercase. It is strange though the MySQL related books I've read have kept 
table and column names to lower case, maybe because they're easier to read. 
(Hey, maybe I'll blame this on Paul.bg) I only wish MySQL could have the 
case insensitive names. I see no point in having case sensitive table and 
column names.

Brent

(Oops.. I've got to go. There is someone knocking on the door...)


_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Case Sensitivity Question

[Benjamin Pflugmann]

Hi.

On Sun, Feb 17, 2002 at 09:10:24AM -0700, [EMAIL PROTECTED] wrote:
 Since my first posting of this came out all screwed up,
 
 I currently have a PHP script performing the following query:
 mysql_query(SELECT * FROM Stuff WHERE Release LIKE \%$var2%\,$var1)
 
 It works great except that the search is coming out case sensitive.
 According to the MySQL Docs all searches are done without case
 sensitivity.

Well, not completely. The default is case insensitivity, but depending
on the context, you get case sensitive comparisons. See also
http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#String_comparison_functions
(and the following section)

E.g. the behaviour you describe would be expected if Release has a
column type that is regarded BINARY (like BLOB).

 I just have 2 questions:
 
 1) I think the syntax I'm using is correct but incase it's not what is
 the correct syntax for a case insensitive search?

See the next section of the above link. It suggest casting to
uppercase for forcing case insensitive compares.

 
 2) What version of MySQL did the default of case insensitive searches
 begin as I'm using 3.23.31.

LIKE is case insensitive per default since 3.20.17 according to
http://www.mysql.com/documentation/mysql/bychapter/manual_News.html

Bye,

Benjamin.

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: case sensitivity issue

[Werner Stürenburg]

 I'm having trouble with what appears to be a case sensitivity issue.
 Essentially, when I use the right case for a table name, the query fails,
 but when I use the wrong case, the query succeeds.

Well, it seems to depend on the system you are using. I noticed
that when I created tables through phpMyAdmin on my local Win
system, that the table(=file) was created on disk lower case only.

It doesn't matter on Win, as the file system is not case
sensitive, but it does on Linux. So you have to be careful if you
transfer tables.

I didn't see the create table syntax make any mistakes, so I
think it is phpMyAdmin, but I don't know - I didn't care to
check.

Check the tablename(=filename) and rename the table or change the
name in your scripts.


-- 
Herzlich
Werner Stürenburg

_
ISIS Verlag, Teut 3, D-32683 Barntrup-Alverdissen
Tel 0(049) 5224-997 407 · Fax 0(049) 5224-997 409
http://pferdezeitung.de



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Case Sensitivity Problem

[Cal Evans]

Select Name, lower(name) as sortOrder from users order by sortOrder;

Not exactly what you wanted, but closer.

Cal
http://www.calevans.com
 

-Original Message-
From: Kif Henderson [mailto:[EMAIL PROTECTED]]
Sent: Friday, March 09, 2001 7:38 PM
To: [EMAIL PROTECTED]
Subject: Case Sensitivity Problem


Hi,

Can somebody PLEASE help me

If I send a query to get a list of names, like this:
 SELECT Name FROM users ORDER BY Name,

I get a list of users like this...

Bob
Jon
Mark
Sally
andy
lucy
mary
steve

when what I REALLY want is this...

andy
Bob
Jon
lucy
Mark
mary
Sally
Steve

Can someone give me an idea on how I need to change my query in order to
achieve this ?


Thanks
Keith



Netrickery
Website Designers


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php