Re: Postal code searching
That still leaves the question, what are the actual rules/business logic by which you want to group things and get their abbreviations? Are you adhering to Royal Mail/Post Office residual selection/direct selection rules, or do you have your own scheme? It seems like the latter ... the RM rules wouldn't abbreviate WC1H to WC1. Have you tried REGEX matching based on your rules? Fuzzy :-) On 24/04/2012 14:16, Neil Tompkins wrote: At the moment im concentrating on london postal codes but future would be us zip codes too On 24 Apr 2012, at 18:09, Rick James rja...@yahoo-inc.com wrote: Please be more precise about the rules. In the US, 12345-6789 would become 12345. This would follow a different rule. Is your rule stop after the first digit? That gets quite messy in SQL, and would be better done in an application code. See also http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring-index RLIKE can distinguish digits from letters, but won't help you isolate them. -Original Message- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Tuesday, April 24, 2012 9:11 AM To: [MySQL] Subject: Postal code searching Hi I've a number of different postal codes in a system for example WC1B 5JA WC1H 8EJ W1J 7BX W1H 7DL NW1 1NY I can use like statements for example SELECT * FROM postal_codes WHERE zip LIKE 'W1%' giving me W1J 7BX W1H 7DL In addition I have a number of abbreviated postal codes like W1 WC1 WC2 NW1 Now, if I know the postal code W1J 7BX what is the best way using a MySQL query to get the abbreviated postal codes W1. Same if I have the postal code WC1H 8EJ, how do I get the abbreviated postal codes WC1 Can I use any matching patterns ? Thanks, Neil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Help with export and import into Oracle
machiel.richards wrote: Good day guys [snip] . Each item in the text field is added in the field by entering the country name then pressing enter and then entering the next, etc . When exporting the data to a file (even when enclosing each field within quotes) it still writes the control characters causing each item to be read as a different line and thus the import into Oracle fails. Any idea on how we can resolve this as the process needs to be cronned to run on a weekly basis and thus we need to get this process resolved. You haven't described what process you're using to read the file for the Oracle import - all of Oracle's interfaces (oci, SQL, PL/SQL, load utilities like SQL*Loader and imp/impdp, external tables, etc.) can handle multi-line records like this. Given you're dumping to a file, it's mostly likely you're using SQL*Loader (i.e. sqlldr). The INFILE clause for the control file includes an os_file_proc_clause which let's you set the record delimiter, and override the default end of line behaviour. See http://www.orafaq.com/wiki/SQL*Loader_FAQ#How_does_one_load_records_with_multi-line_fields.3F for an example. If you're not using SQL*Loader, then more info would be required. Ciao Fuzzy :-) Dazed and confused about technology for 20 years http://fuzzydata.wordpress.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Unable To Remove User
Carlos Williams wrote: On Tue, Sep 29, 2009 at 11:36 PM, mos mo...@fastmail.fm wrote: Why don't you just say Drop User carlos? Also are you logged in as root? In my original message to the board I demonstrated the error I get on my server when I attempt to run the 'drop user 'carlos'@'localhost'; command. Why can't I remove this user from MySQL? [ snip ] Database changed mysql select User, Host from user; +++ | User | Host | +++ | carlos | localhost | | root | localhost | +++ 2 rows in set (0.00 sec) Carlos, is this output exactly as it appeared when you ran the command? Specifically, that extra carriage return after localhost for the user carlos? I wonder if the host value is actually localhost followed by a carriage return, which is why it isn't found when you try to drop the user using the 'carlos'@'localhost' value. Try this select statement to confirm: select user, host from user where host = 'localhost'; If you don't see carlos returned as a user, this is potentially the problem. If that's the case, use Claudio's suggestion to remove the user manually. Ciao Fuzzy :-) Dazed and confused about technology for 20 years http://fuzzydata.wordpress.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MYSQL startup problem
You have ten half-open connections (in SYN_SENT state), and a new connection attempt is giving you error 10055 which is windows' way of saying it can't allocate a buffer for a new connection. Are you running XP SP2 or SP3 or Vista? Microsoft introduced a throttle on in these versions, if more than ten are in the process of handshaking (e.g. SYN_SENT state). In theory this is to limit SYN floods and other connection attacks. To confirm if this is your problem, check your windows event viewer for system events with event id 4226: TCP/IP has reached the security limit Your ten handshaking connections are all to port 445 ... which is microsoft-ds (aka Active Directory). If the above matches your scenario, shut down whatever services/apps are doing this on the box, and MySQL will be able to give you a connection. Ciao Fuzzy :-) Dazed and confused about technology for 20 years http://fuzzydata.wordpress.com/ BAJAJ POOJA wrote: Hi, Pls. find response below: C:\wamp\mysql\binnetstat -an Active Connections Proto Local Address Foreign AddressState TCP0.0.0.0:25 0.0.0.0:0 LISTENING TCP0.0.0.0:80 0.0.0.0:0 LISTENING TCP0.0.0.0:1350.0.0.0:0 LISTENING TCP0.0.0.0:4450.0.0.0:0 LISTENING TCP0.0.0.0:2701 0.0.0.0:0 LISTENING TCP0.0.0.0:2702 0.0.0.0:0 LISTENING TCP0.0.0.0:3306 0.0.0.0:0 LISTENING TCP0.0.0.0:3389 0.0.0.0:0 LISTENING TCP0.0.0.0:8081 0.0.0.0:0 LISTENING TCP0.0.0.0:13468 0.0.0.0:0 LISTENING TCP127.0.0.1:1074 0.0.0.0:0 LISTENING TCP172.21.138.200:139 0.0.0.0:0 LISTENING TCP172.21.138.200:1074172.21.136.21:139 TIME_WAIT TCP172.21.138.200:3389172.21.138.11:2590 ESTABLISHED TCP172.21.138.200:4534172.21.136.21:135 TIME_WAIT TCP172.21.138.200:4535172.21.136.21:1026 TIME_WAIT TCP172.21.138.200:4537172.21.136.21:135 TIME_WAIT TCP172.21.138.200:4538172.21.136.21:389 TIME_WAIT TCP172.21.138.200:4575172.21.13.139:445 SYN_SENT TCP172.21.138.200:4576172.21.13.140:445 SYN_SENT TCP172.21.138.200:4577172.21.13.141:445 SYN_SENT TCP172.21.138.200:4578172.21.13.142:445 SYN_SENT TCP172.21.138.200:4579172.21.13.143:445 SYN_SENT TCP172.21.138.200:4580172.21.13.144:445 SYN_SENT TCP172.21.138.200:4581172.21.13.145:445 SYN_SENT TCP172.21.138.200:4582172.21.13.146:445 SYN_SENT TCP172.21.138.200:4583172.21.13.147:445 SYN_SENT TCP172.21.138.200:4584172.21.13.148:445 SYN_SENT UDP0.0.0.0:69 *:* UDP0.0.0.0:445*:* UDP0.0.0.0:500*:* UDP0.0.0.0:1025 *:* UDP0.0.0.0:1026 *:* UDP0.0.0.0:1221 *:* UDP0.0.0.0:4500 *:* UDP0.0.0.0:8081 *:* UDP0.0.0.0:8082 *:* UDP127.0.0.1:123 *:* UDP127.0.0.1:1900 *:* UDP127.0.0.1:4542 *:* UDP172.21.138.200:123 *:* UDP172.21.138.200:137 *:* UDP172.21.138.200:138 *:* UDP172.21.138.200:1900*:* C:\wamp\mysql\binmysql -u root -p Enter password: * ERROR 2003: Can't connect to MySQL server on 'localhost' (10055) C:\wamp\mysql\binmysql -u root -p Enter password: ERROR 2003: Can't connect to MySQL server on 'localhost' (10055) -- Thanks Regards Pooja Bajaj Alcatel-Lucent, India Email:pooja.ba...@alcatel-lucent.com Phone:+124-413-3078 | Onnet: 2721-3078 From: Chandru [mailto:chandru@gmail.com] Sent: 12 December 2008 13:40 To: BAJAJ POOJA Cc: chaim.rie...@gmail.com; mysql@lists.mysql.com Subject: Re: MYSQL startup problem Hi, I think you may have to get me the output of netstat -an (this only tells the ports that are all listening). sorry for the confusion. can you connect using mysql -u root -p and also using mysql -u root -p -h 127.0.0.1 one thing that i find here is that you have missed the -p option which prompts for password. Regards, Chandru www.mafiree.com On Fri, Dec 12, 2008 at 1:39 PM, BAJAJ POOJA pooja.ba...@alcatel-lucent.com wrote: Hi, Pls. Find response below: C:\wamp\mysql\binMysql -h localhost -u root ERROR 2003: Can't connect to MySQL server on 'localhost' (10055) -- Thanks Regards Pooja Bajaj Alcatel-Lucent, India Email:pooja.ba...@alcatel-lucent.com mailto:email%3apooja.ba...@alcatel-lucent.com Phone:+124-413-3078 | Onnet: 2721-3078
Re: Importing MS SQL exported CSV files adds spaces
Ali, Saqib wrote: I exported a large data set from from Microsoft SQL server in CSV format. However whenever I try to import that data to a a mySQL server running on Linux, it adds a space between each character in each field. Essentially: Saqib Ali becomes S a q i b A l i I have tried to use the dos2unix cmd on linux, but that didn't help either. I am using the following SQL to import the data: LOAD DATA LOCAL INFILE '/home/saqib/data.csv' INTO TABLE apps FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\n'; You SQL Server export will be Unicode, UTF8 or UTF16 ... thus the appearance of the extra spaces. (That's just coincidence, they're not really extra spaces ... the text editor you're viewing the file with is treating the data as ASCII, and not recognising the multi-byte nature of the characters.) Change your MySQL character set for the apps table appropriately (i.e. make it match the character set of the data), and see what happens. Alternatively, create a staging table with the right character set to load the data into first, and then convert it using an insert ... cast... style statement into your apps table (basic ETL). Ciao Fuzzy :-) Dazed and confused about technology for 20 years http://fuzzydata.wordpress.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migration from Oracle to MySQL
Tangirala, Srikalyan wrote: Hi All: Could you provide some more information about Oracle limitations, MySQL limitations, Oracle vs. MySQL etc? Sure, let's play devil's advocate for a minute. Some things unique to MySQL that Oracle does not offer include: - Storage engines, choices like InnoDB, MyISAM Cluster, give you specialized transactional, search/read optimized and highly available engines for storing your data Storage engines are unique to MySQL? yes. Is that good? YMMV. Most of the purported benefits can be achieved with Oracle's features without the compromises of balkanised storage engines. You're right, they're not offered by Oracle, or anyone else ... there's a reason no other database bothers with storage engines - they got storage right the first time :-) (ooh ... the flames I'll get for that :-) ). Sure, non-volatile data in a MyISAM table can be read at the speed of light, and handle the odd insert. Funnily enough, a text file has the same properties. They both suck for non-trivial concurrent transactions. I'd suggest taking a look at parallel DML, nologging, MVs, partitioning, direct-path insert, appended insert, RAC, ASM, ASSM, etc. etc. etc. in Oracle for more perspective - Fast connections Nope, not unique. Prespawned connections in Oracle are about as fast as it gets for any db, short of using a cached connection. - Easy replication We'll, if by unique and not offered by Oracle you mean you get to experience the MySQL pain of sync'ing the data to start with by any one of numerous half-baked manual methods, sure. Silly old Oracle totally automates that, even giving you several handy GUI or sql options depending on your preference. MySQL definitely wins on the does half the job criteria. - Overall ease of use Easy for who? You're absolutely right for simple installs; a quick db to support a simple web page; the persistence layer for a million and one open source apps that could have chosen any db (mysql, postgres, sqllite, jet, bdb, isam, you name it). But have you ever tried to reorg your physical storage in MySQL with the system online? Get the optimiser to do something intelligent with subselects? Handle transaction semantics across storage engines? Easy isn't the word that springs to mind. (OK, I'm all suited up with the asbestos ... flame away :-) ). Ciao Fuzzy :-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Convertion ORACLE query to MYSQL
On 11/9/06, ViSolve DB Team [EMAIL PROTECTED] wrote:
Hi Experts,
We are in the process of converting Oracle administration commands into MySQL
for some purposes. We dont know how to convert the following Oracle commands to
MySQL. How to do that..?
1. ALTER USER spec TEMPORARY TABLESPACE temp_ts;
There's no equivalent concept in MySQL to a temp tablespace. The key
with them in Oracle is actions performed in a temp tablespace aren't
logged (sorts and hashes that overflow the memory), and the files that
constitute them aren't required for recovery.
2. DROP USER jbossjms1 CASCADE;
No equivalent to the cascade option ... so create a script-generating
script, or drop the database if you've equated schema with database.
3. DROP TABLESPACE jbossjms1 INCLUDING CONTENTS;
You normally don't need to muck around with a storage engine's
low-level tablespace management. You might want to work out what
objects this would drop, and do that instead.
4. CREATE TABLESPACE jbossjms1DATAFILE '${JBOSSJMS1}' SIZE 100M REUSE
AUTOEXTEND ON MAXSIZE UNLIMITED;
Ensure the files in your innodb_data_file_path have the autoextend
property set, and total atleast 100MB in size. The Oracle syntax
above uses ASM-based storage, which has no equivalent in MySQL, so
forget that bit
5. CREATE USER jbossjms1 IDENTIFIED BY jbossjms1DEFAULT TABLESPACE jbossjms1;
As per normal user creation ... just ignore the tablespace bit.
Ciao
Fuzzy
:-)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
