Regular Expressions in MySQL -- how to show addresses that have the same numbers?
Hi All,
We've got a database (legacy, running MySQL 4.1.22 Standard) of
constituents. We want to search through it for duplicates, and I've found
that a good way to generate a list of potential duplicate addresses is to
look for addresses that have matching street numbers, zipcodes, and last
names. For instance, consider the following two records:
John Smith, 123 E Easy St, Springfield, IL 12345
John Smith, 123 East Easy Street, Springfield, IL 12345
They're not a letter for letter match, but if you just compare the last
names ("smith"), and the numeric components of the address ("123" and
"12345"), they would match.
I am wondering, is there a way to search through a table like this (all
fields are varchar):
fname, lname, address, city, state, zip
Using regular expressions, to show any records which match another record on
lastname, street number, and zip? I could do this easily enough in a client
language like PHP, C#, Java, etc., but I was hoping I could do it in SQL
directly. Is this possible?
If it's possible, but only with MySQL 5 or better, I could probably export
the database, and import it into a newer version of MySQL. But it would
naturally be nicest if we can do on the existing server.
Suggestions?
-Josh
--
View this message in context:
http://www.nabble.com/Regular-Expressions-in-MySQLhow-to-show-addresses-that-have-the-same-numbers--tp15923015p15923015.html
Sent from the MySQL - General mailing list archive at Nabble.com.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Left join results - can we make it produce at most 1 row?
"Wolfram Kraus" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Use GROUP BY with GROUP_CONCAT: > http://dev.mysql.com/doc/mysql/en/group-by-functions.html Thanks, I'll take a look and see if I can figure that out. I wasn't aware of the GROUP_CONCAT function; it looks like that might be just what I need. Any examples that apply to my situation would be greatly appreciate! :-) Sincerely, -Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Left join results - can we make it produce at most 1 row?
Hi All, I have two tables in a children's program registration system, parent and child. 1 row in the parent table corresponds to 1 or more rows in the child table. Right now, when I do SELECT * FROM parent LEFT JOIN child ON parent.ID = child.parentID I get multiple rows for each parent if they registered more than one child. This is normal and expected I know, but I am wondering is there a way to return the children on the *same* row, so that the first bit of the row is the parent's information, and the second part of the row is something like "child1.fname, child1.lname, child2.fname, child2.lname", etc. I'm not really tied to LEFT JOIN if I have to use another syntax. I am also not concerned about speed, because this query would be run at most a few times per week by an admin. It can be inefficient - as long as it won't crash the server! :-) Any help? Sincerely, -Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Difference between 'LIKE' and '='
I should have included the context of this question when I originally asked
it, but I'll include it here now with my apologies:
The reason I am asking is because I have a search method in a PHP class that
searchs a table based on a specific field. By way of example, you would do
something like this:
$result = $Contacts->search('lname','smith');
Now right now, all it does is run the query:
SELECT * FROM contacts WHERE lname = 'smith';
Then stores the result set in an array and returns it.
What I am wondering is, is there any reason why I should now change the
query to ($field and $searchFor get filled in with their respective values):
SELECT * FROM contacts WHERE $field LIKE '$searchFor';
This would allow me to do this:
$Contacts->search('lname','smith%');
And I would get people with lastname Smith and Smithers and Smithereens,
etc.
I'm aware of security concerns like SQL injection and so forth and am
handling them, but I've omitted all that code for the sake of brevity. No
need to warn me about all that.
-jb
"Joshua Beall" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Hi All,
>
> From what I understand the LIKE keyword allows you to do things like
> include wildcards and match against regular expressions.
>
> But I have a question: is there any difference between the following?
>
> SELECT lname, fname FROM contacts WHERE lname = 'smith';
> SELECT lname, fname FROM contacts WHERE lname LIKE 'smith';
>
> I'm having difficulty figuring out the difference between these two
> queries, if there is any. Can someone clarify?
>
> Sincerely,
> -Josh
>
>
>
> --
> 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]
Difference between 'LIKE' and '='
Hi All, >From what I understand the LIKE keyword allows you to do things like include wildcards and match against regular expressions. But I have a question: is there any difference between the following? SELECT lname, fname FROM contacts WHERE lname = 'smith'; SELECT lname, fname FROM contacts WHERE lname LIKE 'smith'; I'm having difficulty figuring out the difference between these two queries, if there is any. Can someone clarify? Sincerely, -Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Clustering and a large database
> -Original Message- > From: John McCaskey [mailto:[EMAIL PROTECTED] > Sent: Monday, December 06, 2004 17:01 > To: Joshua Beall > Cc: [EMAIL PROTECTED] > Subject: Re: Clustering and a large database > > > Yes, mysql clustering is a ram only database. It does not > make sense to use it if you have a very large database. > > You can use master/slave functionality and use whatever table type you > like. But using the newer clustering technology you have no > choice but to use the ndb table type which is ram only. Once the clustering functionality is available with other table types (which are not RAM only), then clustering will be feasible for larger databases? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Clustering and a large database
"Klaus Berkling" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I have started to import our data. I gather from the manual that tables > are stored in RAM. I am trying to import a database with 11 tables with > about 7 million rows. If I follow the math in the manual, one row will > use 32KB, I would need 224 TB of RAM. What part of the manual leads you to believe that the entire contents of every table needs to be stored in RAM? I was not aware of this requirement. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Selecting a random row
> How many rows do you anticipate that the table will have? Have you > tested this on your server? I would have to bet that if you > have only a few K rows that speed/performance will not be an issue. I doubt I will have more than 100. Perhaps I shouldn't worry about it, then. > The way to > enhance this is by selecting an indexed value, such as the following > where `foo` is indexed > > SELECT `foo` FROM fortunes ORDER BY RAND() LIMIT 1 Why does this help? From the MySQL book I have, the reason ORDER BY RAND() is slow is because "for each record in the table a random number must be generated. Then all random numbers are sorted so that the first n records can be returned. Is this correct? If so, how does selecting an indexed column help this, won't it still need to perform all those operations (generate rand-nums, then sort) regardless of the index? Jfyi the book I am referring to is "MySQL" by Michael Kofler. I'd give the Amazon link but they appear to be down at the moment. The ISBN is 1-893115-57-7 for any who want to look it up elsewhere/when amazon.com comes back up. -jb -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Selecting a random row
Hi All, I understand that I can get a random row out of a table by doing something like SELECT * FROM fortunes ORDER BY RAND() LIMIT 1 But I have also been told that this is a very slow operation. I am building a script that will display a random saying, user testimonial, whatever, on a web page. Since this is a public page (i.e., not an admin backend), I have to be concerned about speed. What is the best way to get a random row out of a database for this sort of application? It's the sort of thing you see all the time, so I'm sure others have thought about this before. Any pointers? Sincerely, -Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Searching a table and replacing all instances of a string with another
Hi All, I would like to search through all fields in a table, and anytime a search string comes up, have it replace it with another string. By way of example, let's say I wanted to replace every occurence of 'Peter' with 'Paul' - can I do this purely with SQL? I know I could do it in PHP fairly easily, but I am wondering if there is a way I can just feed a query to MySQL that will take care of things. Possible? Sincerely, -Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Maximum row size for MyISAM table type
Hi All, I am a bit confused by the MySQL documentation on this subject. From http://dev.mysql.com/doc/mysql/en/Storage_requirements.html "The maximum size of a row in a MyISAM table is 65,534 bytes. Each BLOB and TEXT column accounts for only five to nine bytes toward this size." So, the maximum size is 64k, but you can go over that limit by using BLOB or TEXT types, because although they can hold up to 4gb (2^32 bytes for LONGBLOB/LONGTEXT types), they still only contribue 5 to 9 bytes? What? Am I reading that correctly? Thanks for any clarification, -Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Checking the status of a field, and ensuring it is not updated before I change it
Hi All, I have the following situation: I want to check a row in a database (list of jobs that need to be run). So, my script checks the status field, and if it is not started, then it marks it as locked and running, and when it finishes, it marks it as completed. Now, here is what I am concerned about - because there may be many processes running at any one time, it seems feasible to me that two instance of my script might simultaneously issue the SELECT statement, and seeing that the job is not started, then subsequently issue UPDATE statements. Both processes "think" they are the only ones running. How should I deal with this? It seems to me that I need some sort of query that, all in one transaction, first locks the row so that no other processes can access it, then checks to see if it is running/completed or not. If it is not yet running/completed, it starts the job, updates the database row to mark the job as running, and then unlocks the row. Is this what I should do? I'm not sure how to do this. Any thoughts? Any help would be greatly appreciated! Sincerely, -Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Meaning of 1:1, 1:1 generalization, 1:n, 1:n non identifying, n:m
Hi All, I've been taking a look at DB Designer 4, and looking through the documentation (http://www.fabforce.net/dbdesigner4/doc/index.html) I am a little unclear on some of their nomenclature: '1:1' - Ok, one to one. Got it. '1:1' generalization - Don't know this. Obviously different somehow from one to one, but how? '1:n' - One to many, I assume. '1:n non identifying' - Nonidentifying? What does this mean? 'n:m' - Many to many? Again, not sure. Can anyone help clarify? Thanks! -Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Graphical program to describe table relationships
Hi All, This is more of a general SQL database question than a MySQL question, but since I use MySQL almost exclusively I thought I would ask here. Up until now, when designing a database, I always plotted out the relationships on paper, with pen/pencil. I've filled large pieces of paper with all the tables and their relationships. However, now I will be doing one of these large projects, and the client wants to see how I propose to do all the relationships. I would like to have something more professional than a 24"x36' piece of paper with my scribblings all over it. This has been quite good enough for me, although it is decidedly low-tech. I've seen commercial applications that allow you to do this sort of thing, but I do not have much of a budget. I am wondering if anyone can direct me to a free program that allows me to do this sort of thing? I would prefer some sort of client side application that will allow me to click and create new tables, relationships, and so forth. It does not need to actually *do* anything, i.e., it does not need to create or execute SQL queries. I just want to model the relationships. Suggestions? What do other people use to model their database? Thanks for any input! Sincerely, -Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Calculated fields - is there any way to do this?
MySQL 4.0.20-standard, but willing to upgrade if I have to -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Calculated fields - is there any way to do this?
Is there any way in MySQL to have calculated fields? E.g., a make "expiration" equal to the "lastPaid" date, plus 1 year. Whenever lastPaid gets updated, expiration can be updated as well. Is this possible? I know it could be implemented client side, but I am wondering if it could be done directly in MySQL? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
What is the difference between creating one key with multiple fields, and creating multiple keys, one per field?
Hi All, I have noticed that in phpMyAdmin, when I create a table and select the columns I want indexed, if I select two or more columns (say "fname" and "lname") to be indexed, then when the table is created, instead of two keys, I have only one key, and it contains both the fname and lname columns. If I had forgotten to select those fields to be indexed when I created the table, I would click the "index" link (for both columns) in phpMyAdmin's structure layout page for that table, and I would then wind up with two separate keys, one for each column. As best I can tell, both ways seem to perform the desired task of allowed faster searching for data in those columns, but I am not sure what the difference is? Can anyone enlighten me? Is one way more "correct?" What are the pros and cons? Thanks for any insights! Sincerely, -Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Automatically optimizing a table - how should I so this?
"Per Andreas Buer" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > "Joshua Beall" <[EMAIL PROTECTED]> writes: > > > "Daniel Kasak" <[EMAIL PROTECTED]> wrote in message > > news:[EMAIL PROTECTED] > >> Is there any particular reason why you think the table will need > >> optimizing, or do you just want everything to be super-optimized? > > > > Because when I pull up phpMyAdmin, and it says there is 3,768 bytes of > > overhead, I just feel *dirty*! Overhead, after all, is a Very Bad > > Thing! > > Yeah. And doing a full table optimization after updating one single row > does not at all add any overhead. Not at all. :P It doesn't count if you are there watching it. Just like when you watch a tea kettle; it doesn't boil. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Automatically optimizing a table - how should I so this?
"Daniel Kasak" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Is there any particular reason why you think the table will need > optimizing, or do you just want everything to be super-optimized? Because when I pull up phpMyAdmin, and it says there is 3,768 bytes of overhead, I just feel *dirty*! Overhead, after all, is a Very Bad Thing! ;-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Automatically optimizing a table - how should I so this?
Hi All, Is there a way to automatically optimize a table anytime data is changed. I have a table that only has changes made to it occasionally (average over a 1 week period is probably 1 row is updated each day), and I would like it to automatically optimize the table, rather than having to do it manually. I know that I could setup a cron job to do this, or modify my client scripts to automatically optimize the table after doing an insert/update/delete, but I am wondering if there is a better way to do this. Is there perhaps a change I can make in the table structure that will cause it to auto-optimize whenever data changes are made? Thanks for any suggestions! -Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Integers - How would you enter 112,249?
"David Blomstrom" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > When I try to import my csv file in MySQL-Front, it > appears to get hung up on every integer - or perhaps > it's just every integer in the first integer field. > > When it cites an error, it only lists the numerals > after the comma. For example, it says 249 for this > sample from my csv file: > > "112,249","al","Calhoun","county", AFAIK 112,249 is not an integer. It is a string. If you need to have the commas, you need to store it as a string. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
"table marked as crashed", then repaired by "check"?
Hi All, MySQL 4.0.18-standard on RHEL3, 2.4.21-15.ELsmp kernel. I have a table on one a moderate traffic site, and yesterday I had my client notify me that some of the pages were not working. Note that most of the page content is stored in a table called "pageContent". Now, about half the pages worked, and about half of the pages displayed an error that was something like "error opening file pageContent.MYI". All the other tables were working fine, and when I pulled up phpMyAdmin to look at the database, it listed the pageContent table as being "in use", and wouldn't let me look at any of the data. Trying to view the structure gave me the same error about not being able to open the pageContent.MYI file. Hmm, strange... I tried a "CHECK TABLE `pageContent`", and it returned Msg_type of "warning" and Msg_text of "table is marked as crashed". Great. But, when I went back to the table list of phpMyAdmin, it no longer displayed the "in use" message for the pageContent table. Did running a CHECK TABLE repair the table somehow? I did not know it actually performed any repairs. But a subsequent CHECK TABLE return Msg_type "status" and Msg_text "OK". Furthermore, I no longer got the message about being unable to open the pageContent.MYI file anymore. What happened? Did running CHECK TABLE `pageContent` repair my table? Is this expected behavior? Thanks! -Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Dumping MySQL result set to a spreadsheet
Hi All, I am wondering if anyone can point me to a utility that will let me dump the result of a MySQL query to a file I can open in MS Excel or (preferably) OpenOffice.org Calc. phpMyAdmin lets you do this if you want to dump a "SELECT * FROM ", but if I want to fine tune it I cannot (unless I am missing something) MySQL CC lets me save the result of a query to a test file "right click->save results", but this format does not quite conform to either Excel or Calc's text format. I could fix it by hand, but before I would do that, I think I would write a PHP script that would generate the right output. But before I spend any time working on doing that, I am wondering if anyone knows of a way to do this already? I am running MySQL 4.0.12-standard on Mandrake 9.2 on the server, and my workstation is an XP Pro box. I use MySQL CC 0.9.1-beta and phpMyAdmin 2.5.0, but am completely willing to upgrade more recent versions of either of these tools, or try a new one. Thanks for any feedback! Sincerely, -Josh p.s. How do I use my newsreader to post to these mailing lists? I see that news.gmane.org seems to carry the mysql lists, but it would not let me post, telling me (even after I was subscribed to the list with the same email address I use in my newsreader): Outlook Express could not post your message. Subject 'Dumping MySQL result set to a spreadsheet', Account: 'news.gmane.org', Server: 'news.gmane.org', Protocol: NNTP, Server Response: '441 You are not allowed to approve postings', Port: 119, Secure(SSL): No, Server Error: 441, Error Number: 0x800CCCA9 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
