Re: case sensitive table names in mysql-5.0.21
Hello, I am using MySql-5.0.21 on Suse-Linux-10, i created tables using lowercase names for example ; mysql create table a (id int); Query OK, 0 rows affected (0.04 sec) mysql select * from A; ERROR 1146 (42S02): Table 'mysql.A' doesn't exist mysql select * from a; Empty set (0.01 sec) I have upgraded from mysql-4.0.17 to 5.0.21 ,and all the ANSI-SQL queries from my application using UPPERCASE table names are bombing, i immediately had to stop 5.0.21 and restart mysql-4.0.17. Is there any way to get around this problem and have a smooth upgrade. Regards Digz The previous version of the App was on Windows(mysql-4.0.17) .this ones on Linux .Does that make a difference ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: case sensitive table names in mysql-5.0.21
Hi, yes it makes a huge difference, as the tables are stored in files and therefore if the filesystem is case-insensitive (Windows) you can use either case in your queries, while on a case-sensitive filesystem (linux etc) you can't. See: http://dev.mysql.com/doc/refman/5.0/en/cannot-find-table.html /Johan Digvijoy Chatterjee skrev: Hello, I am using MySql-5.0.21 on Suse-Linux-10, i created tables using lowercase names for example ; mysql create table a (id int); Query OK, 0 rows affected (0.04 sec) mysql select * from A; ERROR 1146 (42S02): Table 'mysql.A' doesn't exist mysql select * from a; Empty set (0.01 sec) I have upgraded from mysql-4.0.17 to 5.0.21 ,and all the ANSI-SQL queries from my application using UPPERCASE table names are bombing, i immediately had to stop 5.0.21 and restart mysql-4.0.17. Is there any way to get around this problem and have a smooth upgrade. Regards Digz The previous version of the App was on Windows(mysql-4.0.17) .this ones on Linux .Does that make a difference ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Case Sensitive
This isn't caused by MySQL. It's an OS issue. Linux and Unix are case sensitive operating systems so any program you run, unless it is specifically written to be NON-case sensitive, is case sensitive as well. Jack -Original Message- From: Andre MATOS [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 12, 2004 10:53 AM To: [EMAIL PROTECTED] Subject: Case Sensitive Hi List, I have three MySQL servers. One is running on Windows XP, another on MacOS X, and finally one on Linux. All servers have the same version: 4.0.18. Every time when I create tables on the servers Windows XP and MacOS, MySQl creates the database without creating problems with the tables names (is not case sensitive). However, in the Linux I have problems (it follows exactly the name uppercase and lowercase is case sensitive). So, for example: On Windows/Mac: TestABC is created as testabc On Linux: TestABC is created as TestABC 1) How can I disable this in the MySQL server running on Linux? 2) What is better? Disable or enable the others two? By the way, I have the same problem with the usernames and passwords. How can I enable/disable MySQL case sensitive for data? Thanks. Andre -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Case Sensitive
But this issue can be handled by MySQL. Take a look at the lower_case_table_names system variable. With lower_case_table_names=1 on the Linux/Unix server, table names will be stored as lower case. See the manual http://dev.mysql.com/doc/mysql/en/Name_case_sensitivity.html for the details, and be sure to read the last sentence. Michael Jack Coxen wrote: This isn't caused by MySQL. It's an OS issue. Linux and Unix are case sensitive operating systems so any program you run, unless it is specifically written to be NON-case sensitive, is case sensitive as well. Jack -Original Message- From: Andre MATOS [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 12, 2004 10:53 AM To: [EMAIL PROTECTED] Subject: Case Sensitive Hi List, I have three MySQL servers. One is running on Windows XP, another on MacOS X, and finally one on Linux. All servers have the same version: 4.0.18. Every time when I create tables on the servers Windows XP and MacOS, MySQl creates the database without creating problems with the tables names (is not case sensitive). However, in the Linux I have problems (it follows exactly the name uppercase and lowercase is case sensitive). So, for example: On Windows/Mac: TestABC is created as testabc On Linux: TestABC is created as TestABC 1) How can I disable this in the MySQL server running on Linux? 2) What is better? Disable or enable the others two? By the way, I have the same problem with the usernames and passwords. How can I enable/disable MySQL case sensitive for data? Thanks. Andre -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Case Sensitive
you can add `lower_case_table_names = 1` to the my.cnf to ignore table name sensitivity. -Original Message- From: Andre MATOS To: [EMAIL PROTECTED] Sent: 5/12/04 9:53 AM Subject: Case Sensitive Hi List, I have three MySQL servers. One is running on Windows XP, another on MacOS X, and finally one on Linux. All servers have the same version: 4.0.18. Every time when I create tables on the servers Windows XP and MacOS, MySQl creates the database without creating problems with the tables names (is not case sensitive). However, in the Linux I have problems (it follows exactly the name uppercase and lowercase is case sensitive). So, for example: On Windows/Mac: TestABC is created as testabc On Linux: TestABC is created as TestABC 1) How can I disable this in the MySQL server running on Linux? 2) What is better? Disable or enable the others two? By the way, I have the same problem with the usernames and passwords. How can I enable/disable MySQL case sensitive for data? Thanks. Andre -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Case Sensitive on Windows
shaun thornburgh wrote: Hi, I am trying to synchronize two databases - the source is on a FreeBSD web server and the target is on our local Windows 2000 Server machine. I am trying to do this using Webyog (www.webyog.com) which tells Windows to sync with the source machine every hour. Side note: I don't know anything about webyog, but MySQL's built-in replication works so well, I'd recommend looking into using it. However, this is producing errors because the MySQL installation on the local machine isn't case sensitive, therefore it isn't recognizing the same tables. How can I rectify this, I can't find any documentation on the MySQL site regarding this...? Your answer may be here, in the online docs where MySQL case sensitivity is detailed. http://dev.mysql.com/doc/mysql/en/Name_case_sensitivity.html Thanks for your help HTH, Robert Taylor [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Case Sensitive on Windows
At 14:43 + 4/30/04, shaun thornburgh wrote: Hi, I am trying to synchronize two databases - the source is on a FreeBSD web server and the target is on our local Windows 2000 Server machine. I am trying to do this using Webyog (www.webyog.com) which tells Windows to sync with the source machine every hour. However, this is producing errors because the MySQL installation on the local machine isn't case sensitive, therefore it isn't recognizing the same tables. How can I rectify this, I can't find any documentation on the MySQL site regarding this...? Search for lower_case_table_names. However, first determine this: On the FreeBSD machine, do you have tables in a given database that differ only in lettercase. For example, do you have a table named ABC and a table named abc in the same database? If you do, then you'll have some definite difficulty replicating them to a machine that doesn't have case-sensitive filenames. You'll be better off renaming one of the tables, or perhaps making a different choice of slave server host. -- 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 sensitive?
From the MySQL manual A.5.1 Case-Sensitivity in Searches By default, MySQL searches are case-insensitive (although there are some character sets that are never case-insensitive, such as czech ). That means that if you search with col_name LIKE 'a%' , you will get all column values that start with Aor a. If you want to make this search case-sensitive, use something like INSTR(col_name, A)=1 to check a prefix. Or use STRCMP(col_name, A) = 0 if the column value must be exactly A . Simple comparison operations ( =, , = , , = , sorting and grouping) are based on each character's ``sort value''. Characters with the same sort value (like E, e and é) are treated as the same character! In older MySQL versions LIKE comparisons were done on the uppercase value of each character (E == e but E é). In newer MySQL versions LIKE works just like the other comparison operators. If you want a column always to be treated in case-sensitive fashion, declare it as BINARY . See section 6.5.3 CREATE TABLE Syntax . If you are using Chinese data in the so-called big5 encoding, you want to make all character columns BINARY . This works because the sorting order of big5 encoding characters is based on the order of ASCII codes. On Tue, 23 Sep 2003 10:37:24 -0700, Hsiu-Hui Tseng spoke thusly about case sensitive?: Hi, I have questions on string comparison: Table: user_att +-+--+--+-+-+---+ Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ user_id | int(11) | | PRI | 0 | | att_id | int(11) | | PRI | 0 | | value | varchar(200) | | | | | date| datetime | YES | | NULL| | +-+--+--+-+-+---+ - one unique index on user_id and att_id (pk) - one index on att_id and user_id. SELECT * FROM user_att WHERE att_id = 123 and value = 'SANDY'; SELECT * FROM user_att WHERE att_id = 123 and value = 'sandy'; SELECT * FROM user_att WHERE att_id = 123 and value like 'sandy'; The first 2 queries are faster. They result are all the same. However, if I want to retrieve only 'sandy' instead of 'Sandy'. Is there any query to achieve that? Is there anyway to configure mysql to be case sensitive? Thanks! Hsiu-Hui --- Listserv only address. Jeff Shapiro -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Case sensitive indexes
gord barq [EMAIL PROTECTED] wrote: I'm using MySQL 4.1 so how do I define case sensitive collation? Is it on the columns or on the indexes? On column. Look at the collations of your character set using SHOW COLLATION command: http://www.mysql.com/doc/en/Charset-SHOW-COLLATION.html _cs at the and of collation name means case sensitive. Here you can find examples how to set up character set and collation: http://www.mysql.com/doc/en/Charset-examples.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ 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 sensitive indexes
Hi Gord, I understood that searches in general arecase insensitive (but I might be wrong of course). Try to use the word BINARY when creating the table or when making a SELECT. I am not sure how you would have to apply this to the index. Best regards Nils Valentin Tokyo/Japan 2003 8 26 09:33gord barq : I have the following index: CREATE UNIQUE INDEX SongTopic_idx ON SongTopic(songTitle, artist, album); Where songtitle, artist and album are varchar() columns. It appears that this index is not case sensitive. Is that correct? If so, how can I have it so that it is indeed case sensitive because I'm importing lots of data from Oracle which is case sensitive. Thanks. _ Get MSN 8 and enjoy automatic e-mail virus protection. http://join.msn.com/?page=features/virus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Case sensitive indexes
gord barq [EMAIL PROTECTED] wrote: I have the following index: CREATE UNIQUE INDEX SongTopic_idx ON SongTopic(songTitle, artist, album); Where songtitle, artist and album are varchar() columns. It appears that this index is not case sensitive. Is that correct? If so, how can I have it so that it is indeed case sensitive because I'm importing lots of data from Oracle which is case sensitive. VARCHAR column is compared in case-insensitive fashion. You can define column as BINARY if you use 3.23 or 4.0 MySQL server. If you use 4.1 MySQL server you can define case sensitive collation. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ 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 sensitive indexes
I'm using MySQL 4.1 so how do I define case sensitive collation? Is it on the columns or on the indexes? Thanks. From: Victoria Reznichenko [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: Case sensitive indexes Date: Tue, 26 Aug 2003 13:18:19 +0300 gord barq [EMAIL PROTECTED] wrote: I have the following index: CREATE UNIQUE INDEX SongTopic_idx ON SongTopic(songTitle, artist, album); Where songtitle, artist and album are varchar() columns. It appears that this index is not case sensitive. Is that correct? If so, how can I have it so that it is indeed case sensitive because I'm importing lots of data from Oracle which is case sensitive. VARCHAR column is compared in case-insensitive fashion. You can define column as BINARY if you use 3.23 or 4.0 MySQL server. If you use 4.1 MySQL server you can define case sensitive collation. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ Help protect your PC: Get a free online virus scan at McAfee.com. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Case-Sensitive database
I wont work for me, I dont have access to SQL layer. I use JDO In Micorosft SQL Server I can specify the Collate during database creation to make my string case-sensitive. Is there an equivalent configuration for MySQL? Thanks ahead for all suggestions -Dan - Original Message - From: Matt Gostick [EMAIL PROTECTED] To: Dan Tran [EMAIL PROTECTED] Cc: MySQL List [EMAIL PROTECTED] Sent: Monday, March 31, 2003 9:41 AM Subject: Re: Case-Sensitive database I'm not sure if you can do it globally... but here is what I do: select passwd from blerg where login = Matt and binary login = Matt; I do it twice b/c the binary operation is quite slow and doing the normal equate first speeds it up significantly. Doesn't exactly answer your question... sorry. Matt Gostick. On Mon, 2003-03-31 at 11:23, Dan Tran wrote: Hi, how do I configure mysql to be Case-Sensitive globally? (ie where, like, clauses work with case-sensitive strings Help is greatly appreciated. -Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Case-Sensitive database
I'm not sure if you can do it globally... but here is what I do: select passwd from blerg where login = Matt and binary login = Matt; I do it twice b/c the binary operation is quite slow and doing the normal equate first speeds it up significantly. Doesn't exactly answer your question... sorry. Matt Gostick. On Mon, 2003-03-31 at 11:23, Dan Tran wrote: Hi, how do I configure mysql to be Case-Sensitive globally? (ie where, like, clauses work with case-sensitive strings Help is greatly appreciated. -Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Case-Sensitive database
On 31 Mar 2003 at 9:49, Dan Tran wrote: I wont work for me, I dont have access to SQL layer. You can declare your columns as VARCHAR BINARY rather than VARCHAR in your table creation. Another possibility might be to set your character set for the database to latin1csas rather than the default latin1. It's mentioned in Paul DuBois's MySQL book, but I don't see anything about it on the mysql.com site. I'm not sure if you can do it globally... but here is what I do: select passwd from blerg where login = Matt and binary login = Matt; I do it twice b/c the binary operation is quite slow and doing the normal equate first speeds it up significantly. The binary operation is slow because it's not using the index. If you want a column to be case-sensitive, make it VARCHAR BINARY when you create the table. Then you shouldn't need to double your criteria (you can just use WHERE login = 'Matt'). -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org Phone 202-667-6653 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Case-Sensitive database
Do you happen to know how to change the charset? I have read thru the manual, but I have no clue where to start. Thanks in advance -Dan - Original Message - From: Keith C. Ivey [EMAIL PROTECTED] To: MySQL List [EMAIL PROTECTED] Cc: Dan Tran [EMAIL PROTECTED]; Matt Gostick [EMAIL PROTECTED] Sent: Monday, March 31, 2003 10:13 AM Subject: Re: Case-Sensitive database On 31 Mar 2003 at 9:49, Dan Tran wrote: I wont work for me, I dont have access to SQL layer. You can declare your columns as VARCHAR BINARY rather than VARCHAR in your table creation. Another possibility might be to set your character set for the database to latin1csas rather than the default latin1. It's mentioned in Paul DuBois's MySQL book, but I don't see anything about it on the mysql.com site. I'm not sure if you can do it globally... but here is what I do: select passwd from blerg where login = Matt and binary login = Matt; I do it twice b/c the binary operation is quite slow and doing the normal equate first speeds it up significantly. The binary operation is slow because it's not using the index. If you want a column to be case-sensitive, make it VARCHAR BINARY when you create the table. Then you shouldn't need to double your criteria (you can just use WHERE login = 'Matt'). -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org Phone 202-667-6653 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Case sensitive in selecting records
Hi, I have a situation as follows: There is a table that stores the user name. One user name is 'David' and another is 'david' I want to select only the user with the name 'david'(all small and not 'David') MySQL is case insensitive in selecting records... how to make it case-sensitive in selecting records... SELECT BINARY ... / Carsten -- Carsten H. Pedersen keeper and maintainer of the bitbybit.dk MySQL FAQ http://www.bitbybit.dk/mysqlfaq - 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 sensitive
On 15-Aug-2001 Järkeborn Joacim wrote: Hi, I have the table: CREATE TABLE WI_TEXTLISTUS ( TEXTID VARCHAR(50) NOT NULL, COUNTRY VARCHAR(2) NOT NULL, LANGUAGEVARCHAR(3) NOT NULL, TEXTVARCHAR(250), PRIMARY KEY (TEXTID,COUNTRY,LANGUAGE), INDEX TEXTLISTUS_X1 (TEXT) ); and when I inserts data such as INSERT INTO WI_TEXTLISTUS VALUES ('IUSB-2348a-n00021','US','eng','FUEL TANK SUPPORTS, LH'); INSERT INTO WI_TEXTLISTUS VALUES ('IUSB-2348A-N00021','US','eng','FUEL TANK SUPPORTS, LH'); I get an error: ERROR 1062: Duplicate entry 'IUSB-2348A-N00021-US-eng' for key 1 Is it possible to set mySQL to be case sensitive since it's a small difference between the two? TEXTID VARCHAR(50) BINARY NOT NULL, Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) - 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 Sensitive??
If you add BINARY to the column it WILL make it case sensitive. On Thursday 22 March 2001 22:01, you wrote: Hello All, I have a table with a column called "bus_name" varchar(100) which is used in a Business Name search form. I need to make the Business Name that is entered into the search form case in-sensitive to the column "bus_name" IE. If we have a entry in the column "bus_name" "The Happy Shop", and someone enters "the happy shop" or "THE HAPPY SHOP", it will still return a match. If I add the attribute "BINARY" to the colunm "bus_name" will this make it case-insensitive or is there another way to do this within the SQL query?? TIA, Mike(mickalo)Blezien Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Tel: 1(225) 686-2002 = - 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 Sensitive??
Try Select * from table where upper(bus_name)= upper('whater I am searching for'); Cal http://www.calevans.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of MikemickaloBlezien Sent: Thursday, March 22, 2001 4:02 PM To: [EMAIL PROTECTED] Subject: Case Sensitive?? Hello All, I have a table with a column called "bus_name" varchar(100) which is used in a Business Name search form. I need to make the Business Name that is entered into the search form case in-sensitive to the column "bus_name" IE. If we have a entry in the column "bus_name" "The Happy Shop", and someone enters "the happy shop" or "THE HAPPY SHOP", it will still return a match. If I add the attribute "BINARY" to the colunm "bus_name" will this make it case-insensitive or is there another way to do this within the SQL query?? TIA, Mike(mickalo)Blezien Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Tel: 1(225) 686-2002 = - 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 Sensitive??
On Thu, 22 Mar 2001 22:27:06 +, Jordan Elver [EMAIL PROTECTED] wrote: Ignore this post! My fault, we had something coded wrong in the script.! It works fine now. My apologies to the list! :( If you add BINARY to the column it WILL make it case sensitive. On Thursday 22 March 2001 22:01, you wrote: Hello All, I have a table with a column called "bus_name" varchar(100) which is used in a Business Name search form. I need to make the Business Name that is entered into the search form case in-sensitive to the column "bus_name" IE. If we have a entry in the column "bus_name" "The Happy Shop", and someone enters "the happy shop" or "THE HAPPY SHOP", it will still return a match. If I add the attribute "BINARY" to the colunm "bus_name" will this make it case-insensitive or is there another way to do this within the SQL query?? TIA, Mike(mickalo)Blezien Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Tel: 1(225) 686-2002 = - 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 Sensitive??
On Thu, 22 Mar 2001 17:01:35 -0600, "Cal Evans" [EMAIL PROTECTED] wrote: Thank Cal, I found the problem in our coding, not the query, so it works fine now! My apologies to the list. Appreciate the help :) Try Select * from table where upper(bus_name)= upper('whater I am searching for'); Cal http://www.calevans.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of MikemickaloBlezien Sent: Thursday, March 22, 2001 4:02 PM To: [EMAIL PROTECTED] Subject: Case Sensitive?? Hello All, I have a table with a column called "bus_name" varchar(100) which is used in a Business Name search form. I need to make the Business Name that is entered into the search form case in-sensitive to the column "bus_name" IE. If we have a entry in the column "bus_name" "The Happy Shop", and someone enters "the happy shop" or "THE HAPPY SHOP", it will still return a match. If I add the attribute "BINARY" to the colunm "bus_name" will this make it case-insensitive or is there another way to do this within the SQL query?? TIA, Mike(mickalo)Blezien Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Tel: 1(225) 686-2002 = - 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 Mike(mickalo)Blezien Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Tel: 1(225) 686-2002 = - 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 sensitive selects
A comparison will be case sensitivie, if any sub-part of the expression is case sensitive. There are several ways to solve this: 1) CREATE or ALTER your tables so that the relevant CHAR and VARCHAR fields are of type BINARY; change TEXTs to BLOBs 2) Use a case-sensitive, but neutral function when SELECTing, to force MySQL to perform a case-sensitive comparison / Carsten -- Carsten H. Pedersen keeper and maintainer of the bitbybit.dk MySQL FAQ http://www.bitbybit.dk/mysqlfaq -Original Message- From: Ji Mikul [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 21, 2001 4:01 PM To: [EMAIL PROTECTED] Subject: case sensitive selects Hi all. I have problem with MySQL 3.23.32 on FreeBSD4.2 SELECTs are not case sensitive, any suggestion how to set it ? I'v looked into DOCs, but i'm not so clever from it :(( ... my config options was only ./configure --prefix=/usr/local/mysql Many thaks. Jiri Mikulas [EMAIL PROTECTED] http://www.anglictina.com ICQ: 108 36 60 66 Admin - anglictina.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 - 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 sensitive selects
Ji Mikul wrote: Hi all. I have problem with MySQL 3.23.32 on FreeBSD4.2 SELECTs are not case sensitive, any suggestion how to set it ? I'v looked into DOCs, but i'm not so clever from it :(( ... my config options was only ./configure --prefix=/usr/local/mysql Many thaks. Jiri Mikulas [EMAIL PROTECTED] http://www.anglictina.com ICQ: 108 36 60 66 Admin - anglictina.com There are several ways: 1. When creating tables, use "text" fields instead of "char" or "varchar". 2. When creating tables, use "char binary" or "varchar binary" fields. 3. Cast the field in the query. Ex: SELECT * FROM names WHERE BINARY fname = "Joe"; Most importantly, read the manual. - 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