MYSQL :: String Comparison

String Comparison
I would like to search a column of strings for matches to a given prefix. Is there a way to use existing string comparison functions to do this? Specifically I'm looking for the number of leading characters identical to my (variable) search string:

Example: looking for matches to prefix "Robb"

SELECT names, some_functions(names, "Robb") AS m FROM etc, etc, etc...

| names | m |

| Roger | 2 |
| Robert | 3 |
| Roseanne | 2 |
| Cary | 0 |
| Randy | 1 |
| Robby | 4 |
| Allison | 0 |

Return the highest value of m for which LEFT(names,m)=LEFT("Robb",m).

I've been reading through the section on string comparison function, but can't find anything suitable. But perhaps there is some combination of funtions you could use?

String Comparison
When i update a table i am carrying out a check to see if that entry already exists. This is ok when it is the same case but when it is different it allows it.

select
count( entityid ) as COUNTENTITIES
from
entities
where
code = '%1%'

I have tried LIKE but that didn't work either

What i am after is if there is a code 'c1' in database and someone tries 'C1' i want that to fail because 'c1' is already there. Is this possible?

Mysql String Comparison
I have string in a field like theses: rel-1-0, rel-2-0, rel-3-0,
rel-21-0.....

if comparing them in this way: select 'rel-3-0'>'rel-21-0';
Mysql will return "1" as true, this is false in our knowledge.
I wonder if there is a way to order such string inside mysql so that
rel-3-0 < rel-21-0?

Mysql String Comparison
Using pure mysql syntax is there a way to compare a varchar and check if it starts with a certain character, ie SELECT * FROM databases WHERE name starts_with '+'

String Comparison Implementation
how string comparison is implemented in mysql. (Both in Physical
and Application layer) How queries on string (for example '>' or like
or strcmp) are implemented. Any idea where can I find the open source
for string comparison/ ordering/ implementation? Does it use the sme
structre as strcmp in c?

String Comparison (ORDER BY) With _ (underscore)
I need to select records ordered by a string column. But when I order
by with data containing an underscore, the result is not as expected.

Following probably will make the problem clearer:

When I try this on Oracle:
SELECT LEAST('aa','ab','a_') FROM DUAL;
The results is 'a_'

but when I try this on MySQL
SELECT LEAST('aa','ab','a_');
The results is 'aa'

whereas I would actually need/expect "a_".
For all other _ characters (like "-". "%") it works fine just as
expected.

Replace Function And String Comparison
I am using the replace function:

replace('China and china','china','Hong Kong');

I want to ignore the cases as I compare the strings... How am I suppose
to do that?

Problem With National Characters And String Comparison
I'm trying to select a list of users belonging to a certain city. In PHP I have set up this query:

PHP

$query = "SELECT first_name, last_name FROM users WHERE city = '$city' ORDER BY last_name";

This works just fine when I am searching for cities like New York or London, but when I try cities with national characters in them, like TromsÃ¸ or BodÃ¸, this fails miserably.

The database collation is latin1_swedish_ci both for the table and the table column. The charset in the php code is iso-8859

Format Function In SQL String To Return From Numeric Field In Table To String
How would i form a format function in SQL string to return from numeric field in table to string:

1 --> '001'
5 --> '005'
57 --> '057'
125 --> '125'

Md5 Comparison
UPDATE admin_users SET password = md5(?') WHERE id = 1;
SELECT password = md5(?') FROM admin_users WHERE id = 1;
The result of the last query is "0", which means the values don't match. How can that be? What am I missing? The hashes look just the same when I echo them. Is it a data type problem?

EDIT: I should mention the m5 password hash is stored in a binary(64) column.

Comparison In Db.
Don't know if it's even do-able in MySQL but I kinda think it is.

lets make an example table over a log.

day - action
1 ---- foo
1 ---- bar
2 ---- foo
2 ---- foo
2 ---- bar
2 ---- bar
2 ---- foo

Now I want, with one query, most of all, select the increase in percent and the name of the action that has increased the most from day one to day two. If this isn't possible I would like to select the name, the amount for the action on day one and the increase of the top increaser.

Dates Comparison??
I'm trying to find people from my db based on a given min_date and max_date using:

SELECT . . . WHERE birthdate BETWEEN '".$dd_min_age."' AND '".$dd_max_age."'
but if a range is specified there are no results returned.

If I choose though the same date for min and max then I get the corresponding results.

Sql Comparison Tools
We've got a bunch of fairly long and complex sql statements that we need to compare between our dev and prd environments. The dev environment has been changed by a bunch of patches, while prd has been changed by an incompetent BA. Does anyone know of any tools that can help do this? Standard text/file comparison tools haven't been any help, so we're down to eyeballing them, which is both no fun and prone to error...

Comparison Query?
I have two tables in my database that collect membership/user info. How do I write a query to compare the two and have the query spit out the users that are on one but not the other?

Comparison Query
I have two tables, I have a company table and a client table. The fields in question are as follows

Company.companyid
Company.maincontactid
Contact.contactid

Due to bad design and bad data entry we have a bunch of companies with a contact number but no entry in the contact table. I want to run a query to dump a list of Company.companyid and Company.maincontactid where Company.maincontactid has no corrisponding Contact.contacid

Text Comparison
I'm having a problem with one query. My database has the following
fields: id(int), time(int), and groupfield(enum - 5 possibilities). I
want to select all the rows where the time is between let's say 12 and
24 and the group is specified by the user using a html drop list. The
variable is named $group. Code:

Database Comparison
I am looking for some papers/information that compare relational
databases such as oracle, mysql, sql server etc. I am particularly
interested in their features such as locking mechanisms, integrity
constraints, views... Anyone know where I can find the information?

Float Comparison
I'm writing a simple database with one table and a few fields that lists some books. One of the fields is an Unsigned Float field that lists the book's reading level. Then I have a PHP/web page front end that users can search for books listed in the database.

Basically this is the probelm I'm having:
If a user asks for all the books between the reading levels 3.0 and 3.3 -- MySQL returns every record that has a reading level of 3 up to and including all the books with a reading level of 3.3. However, if a user types in the form they want all the books between 3.0 and 3.2 -- MySQL returns all the 3.0 and 3.1 books, but returns none of the 3.2 level books.

The code is a simple select query that ends up being:
SELECT * FROM MyTable WHERE ReadingLevel >= 3 AND ReadingLevel <= 3.x

Previous Row Comparison
I have a table in this format:

Column A | Column B
1 | 2
1 | 3
1 | 4
2 | 5
.....
.....

What I want to do is something like this:

If in Column A there is a change in the number i.e. from the above table there were many 1s which transitioned into a 2. I want to record this change. I want to extract these two rows into another table. How can I make this possible?

Date Comparison
I have date comparison problem with mysql... why doesn't the date get in the set? (2nd query returns empty set)

mysql> select version();
+------------+
| version() |
+------------+
| 4.0.20-log |
+------------+
1 row in set (0.00 sec)

mysql> SELECT RecordID FROM Record WHERE DATE_ADD(PostDT,INTERVAL 11 HOUR) >= CAST('2005-3-13 00:00:00' AS datetime) AND DATE_ADD(PostDT,INTERVAL 11 HOUR) <= CAST('2005-3-13 23:59:59' AS datetime) ORDER BY PostDT DESC;
Empty set (0.00 sec)

mysql> select DATE_ADD(PostDT,INTERVAL 11 HOUR) FROM Record;
+-----------------------------------+
| DATE_ADD(PostDT,INTERVAL 11 HOUR) |
+-----------------------------------+
| 2005-03-13 12:00:00 |
| 2005-05-27 21:53:30 |
+-----------------------------------+
2 rows in set (0.00 sec)

Date Comparison 2
I want to compare dates in sequential records (in mysql format YYYY-MM-DD) in a db. I have a loop to iterate through a set of records. In this loop, how can I refer to the previous record in the loop?

As in:

for ($i=0; $i <$num_results; i++) {
$row = mysql_fetch_assoc($result);
if ($row('thisDate') != the previous record's date) {
do something
}
}

Date Comparison
In a php_mysql query the following variable:

$ckDate = $year . "-" . $mo_sel . "-01"; produces a string in the format YYYY-MM-DD.

The $year and $mo_sel variables have been posted from a form.
The problem I am experiencing is that in trying to SELECT from a table by comparing the $ckDate variable to the arrDate field in the database, the search returns a result no matter what the comparision date is, as in:

"select * from this_table where 'arrDate' >= '$ckDate'"

No matter what the value of $ckDate, the query returns data.
Does this variable have to be coerced in some manner?

DATE COMPARISON
I was wondering if there is a date function to get the older date between
two dates?. Or smething that indicates me that one date is older than the
other one.I know that I have the function YEAR,MONTH,DAY and I can use them, but I
don't know if exists a function that can do the same.

Comparison Optimization
On to my problem. I'm running a query on a table with over 100,000 rows in it. Basically, my query is performing a "similar" check on books that people have logged in their "book list". It looks through the table and checks to see who has the most number of books similar to you. Here's the query:

select count(a.mem_id) as numMatches,m.username,m.mem_id as memberID from book_list a LEFT JOIN book_list_members m ON a.mem_id=m.mem_id where a.book_id in(224,164,30,43,47,1,6,52,90,45,120,270,...,2442) GROUP BY a.mem_id ORDER BY numMatches DESC LIMIT 50

In the in() comparison, this can be anywhere from 1 integer to 1000 integers, it all depends on how many books a user has in their list. I've indexed "book_id", but unfortunately if there are more than say, 100 elements in the IN() comparison, the query takes anywhere from 1-2 seconds to execute. That's a long time when I have hundreds of users hitting a website and running that query. Is there a better way to do this? Is there a way I can "store" this query result somewhere, and only have it updated every couple hours (cached in other words, for immediate access).

Datetime Comparison
I have a query in a php page that goes something like this:

SELECT * FROM posts WHERE post_time < '$php_var_date' LIMIT 0,1

I'm using this in a blog where I need the previous post's post date. php_var_date is an ISO 8601 formatted date. The post_time is of the data type datetime. While I don't get an error on execution of the query, it keeps returning the date EQUAL to php_var_date instead of the one previous to it. My question is, is this a MySQL glitch or is it something incorrectly set on the server?

Fake Date Comparison
Code:
select * from tareas where STR_TO_DATE( fecha, '%d/%m/%Y' ) = STR_TO_DATE( 19/11/2007, '%d/%m/%Y' )' order by STR_TO_DATE( fecha, '%d/%m/%Y' ) DESC

Yes, an operator wrong, but why ?

Date Comparison Problem
I have a website with a diary of events. I have just come across this problem:

The diary is listed on the site month by month - you can click a month and events for that month will show up. Events have a from date and to date and can span across multiple months. This has all been working fine until I added an event running from december 06 to january 07 - it no longer showed up. Here is how my query looked:

(using december 2006 as the selected page as an example)

SELECT * FROM diary WHERE (MONTH(from_date) <= MONTH(&#55614;&#57158;-12-01') AND MONTH(to_date) >= MONTH(&#55614;&#57158;-12-01') AND YEAR(from_date) = YEAR(&#55614;&#57158;-12-01')) OR (MONTH(from_date) <= MONTH(&#55614;&#57158;-12-01') AND MONTH(to_date) >= MONTH(&#55614;&#57158;-12-01') AND YEAR(to_date) = YEAR(&#55614;&#57158;-12-01')) ORDER BY from_date, to_date
Now looking back it is obvious that this will not work as it is looking for a match in the years. After some research and trial and error I have come up with the following query:

SELECT * FROM diary WHERE (UNIX_TIMESTAMP(&#55614;&#57158;-12-01') BETWEEN UNIX_TIMESTAMP(from_date) AND UNIX_TIMESTAMP(to_date)) ORDER BY from_date, to_date
However this is still not working.

This is really bugging me and any help will be greatly appreciated.

Thanks!

How To Write Like Comparison In Procedures?
this sample procedure takes in an username, searches for number of entries matching the pattern of the username.

delimiter $$
create procedure sampleDB.sampleProcedure( IN m_username varchar(30), OUT m_username_matched_count int )
deterministic
begin
if length(m_username) = 0 then
set m_username = 'jason' #set temporary name if empty
end if

set m_username_matched = 0; #finds number of matches for the given username

select
count(*) into m_username_matched #store into the variable to be returned
from
sampleDB.sampleTable
where
userName like '%m_username%' #this is the statement that does not work#

end$$

TIMESTAMP Operations & Comparison
I'm trying to write a query that will remove all entries older than 30 days, using MySQL 5. The date is stored as TIMESTAMP.

I'm stumped on just how to express "30 days earlier than now". I'm familar with the NOW() function but don't know how to add/subtract dates.

The query is otherwise very simple, something like
DELETE FROM posts WHERE datecreated < [????]

Text With Tab To MySQL - Db Comparison
sorry... i did post first in databases forum... do not know how to delete my thread... so here my questions:
i have a text file with TAB separation.
in this file, i have some products with lets say: RefNr. / Name / Option / Amount
when i open it i see:

SOURCE:

RefNr.NameOptionAmount
001Product 1Black2
001Product 1Black1
001Product 1Black0
001Product 1Green1
001Product 1Green4
001Product 1Green1
002Product 2Black3
002Product 2Black1
002Product 2Black1
002Product 2Green2
002Product 2Green2
002Product 2Green0

etc etc

do you nkow a way to get this instead, after running a script for instance (my question is â€œhow does the script should look likeâ€!):

RESULT:

RefNr.NameOptionAmount
001Product 1Black3
001Product 1Green6
002Product 2Black5
002Product 2Green4

(i could save this text file first to EXCEL if it helps.)

once iâ€™ve done it, iâ€™ll have in a second step to â€œcompareâ€ this file to a mySQL Database.
but in the selected DB table, i wonâ€™t have exactly the same structure.
i mean in my text file iâ€™ll have for instance not only RefNr. / Name / Option / Amount but
RefNr. / Name / Option / Amount / Description / DeliverDate / ImgName
and in my table in the DB iâ€™ll have:
RefNr. / Name / Option / Amount / DeliverDate / Info

i will need to compare the amounts between â€˜RESULTâ€™ and my mySQL table, to get the amount which changed updated in my DB table.
any idea welcome... even if the process is not automated and if i need to do everything manually in phpMyAdmin for instance (i do not even know how to do it in phpMyAdmin!).

Case Sensitive Comparison
How can I make a case sensitive comparision using mysql_query?

For example:

"SELECT * FROM table WHERE User_id = '$user' AND Password = '$password'";

This returns true even if I match "ABCD" with "abcd".

Datetime Comparison Working
I'm having a simple sql to extract data from time to time (service application in Delphi) and am storing the last record's datetime (MySQL datatype) as an example:
20/08/2005 6:04:21 PM

I want to get the newer data and so, next time i run, i'm comparing the last retrieved record's datetime with the SQL data and want to get only the records having a >= value of DTColumn.

***
Laststoredvalue := '20/08/2005 6:04:21 PM'; (am storing as a string)

My sql string is built at runtime where i use the following:

SQL.Add('WHERE t1.DTColumn >= CAST(' + QuotedStr(Laststoredvalue) + ' AS DATETIME)');
***

When i execute the query and read the first record, It's still getting ALL the records right from the 1st one available that has DTColumn value of '10/12/1981'.

Multiple LIKE Comparison Using IN Operator
Is there anyway to compare a field with multiple string values using
LIKE?

Here's the statement I have now:

SELECT * FROM list
WHERE email LIKE CONVERT( _utf8 'hotmail@hotmail.com' USING latin1 )
OR email LIKE CONVERT( _utf8 'john@hotmail.com' USING latin1 )";

I know how to do it using the IN operator,
SELECT * FROM list
WHERE email IN ('hotmail@hotmail.com', 'john@hotmail.com')

but I need to be able to use the LIKE operator with the CONVERT
function. The reason I am asking is because I am trying to match 50+
different email addresses.

Time Comparison Issue
I have a field storing date/time information in the TIMESTAMP form (mm/dd/yyyy hh:mm:ss AM). Is there a way to compare the current date with this stored date? I am trying to select all of the rows where the saved date is before the current date. I used php to generate the current date and tried using < or <=, but they do not seem to be working.

Comparison - SQL Server And MySQL
I'm pretty familiar with SQL Server 2000 and 2005, but not familiar with MySQL at all. SQL Server has a system database called TempDB, which is used specifically to hold temporary tables by anyone on the system, and it will automatically drop those tables once the connection that created it is broken. This allows for greater performance, and manageability of data.

DATE Comparison Issue
I have a field storing a string value of a year/month like this : 200802 in a field called 'display_month' that I need to compare to NOW()...when I run this query:

SELECT display_month,
YEAR(STR_TO_DATE(wd_orders_meta.display_month, '%Y%m')) AS display_year,
YEAR(NOW()) as now_year
FROM my_table
WHERE DATE_FORMAT(STR_TO_DATE(display_month, '%Y%m'), '%Y%m') = DATE_FORMAT(NOW(), '%Y%m')

My query runs and tells returns a record that has a display_month value of 200802 (next feb) essentially saying that 200802 = 200702. It doesn't do any other month, it just seems confused about the year.

I tried using YEAR() for the comparison, but it still returns 2008 = 2007. I tried casting both values as DATE() in the comparison to no avail. I tried CAST() and convert with only errors in the comparison.

I'm stumped...anyone have any thoughts?

Comparison Of Database Performance
I'm trying to justify using MS Access for a simple database where some other folks in our company are pumped on SQL Server. They only know that someone else told them SQL Server is the answer to everything, when in fact it's extreme overkill here IMO.

This database will have a few tables and is mainly used for storing information regarding CAD drawings. We're not talking about a ton of transactions here! Maybe 100 per day PEAK new records or edits. That's a big maybe, on average probably 10 per day or less. I have a similar system I started 3 years ago which has about 1500 records today.

I know Access has a 2gig limit on data and to me this is the only limiting factor to be concerned about. When comparing Access to SQL Server (not even a comparison) it's really about performance right? There will never be tons of transactions and many concurrent users on this database.

Comparison Of Data Types
I have a large map that is represented in a sql database with an incremental index and a value for each entry representing the type of area on the map.I'm looking to optimize the code that stores and retrieves this information.In an attempt to minimize the number of characters that are sent out from the db, I am storing the types as ascii characters that represent the number type and converting them to numbers once retrieved. In this way, I hope to speed it up by not having to send two-digit numbers.So, my question is whether or not this is a good approach. Would sending just simple numbers be just as fast or faster?Or, more basically, I need a comparison of mysql performance when handling the various types of data. it seems like such a comparison has to exist somewhere.

Date Comparison Trouble
Spent the last 30 mins looking for a solution and haven't found one so:
The table looks like:

ID
aDate
Days - days in advance

1 | 2008-03-10 12:00:00 | 7

Im trying to do the following:
SELECT table. *
FROM table
WHERE aDate >= '2008-03-(03+Days) 11:30:00'

Basically so i can feed a date and it uses that date plus whatever is in the days fields, simple i know!

Query By Column Comparison
I'm trying to pull a row by comparing that the numerical value of one column is greater than the numerical value of other columns. The manual is quite confusing as to how to phrase such a thing, and I haven't done much more than simple queries up until now.
MySQL version: 4.0.22

What I started with that doesn't work:

select * from someTable where col1 > col2 and col1 > col3 and col1 > col4 order by someThing limit 1

I'm basically trying to grab one row where the value of one column is greater than the values of the remaining similar columns.

Equality Comparison Of Char
Is there any difference in speed between an equality comparison of char (or varchar) column values with a constant, and comparison of int column values with a constant?

i.e. would this:

select * from table where col1 = 'blah';

be much slower or faster than

select * from table where col1 = 6;

this?
Obviously the difference is probably miniscule, but this is a query I am planning on running in a lot of loops so it could be important

Database Comparison Tools
I am looking for an easy way to be able to compare two mysql databases for their difference in tables and fields. It would also be nice for an easy way to make changes to the databases and port over information easily.I did search on this and found a couple of products that I found difficult to work with. The two main ones i tried, one froze when it tried to "sync" the databases and the supposed log of the "sync" wasn't very forthcoming and the other product just plain didn't work.I was wondering if anyone out there had this similar need and what the products/scripts were that they used? I would really thin kthere has to a least be a script that compares databases, even if you can't make changes right then and there.

How Do You Replace A String With Another String
How do I write a query in myPHP admin to search through a particular field in a particular table for a string and replace it with another string? The string is only a part of the value of that field. For example, how would I change "Hello there" to "Hi there"?

Date Comparison Not Working (dammit!)
My date_added field is a DATETIME
mySQL 4.1.10

I'm trying to get any record that was created over 72 hours ago and has not activated their account.

PHP

SELECT fname,email,uname,passwd FROM customers WHERE date_added >= DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 72 HOUR) AND activate = 0

That dog just don't wanna hunt. NULL is returned. The query returned in phpmyadmin is

PHP

SELECT fname, email, uname, passwd
FROM customers
WHERE date_added >= DATE_SUB(
CURRENT_TIMESTAMP , INTERVAL 5 MINUTE )
LIMIT 0 , 30

I never signed up for a limit!

And, while I have your attention I want to do the same, except get any record that has been active for 27 days (got to send an email, because I'm gonna dump their a$$ on day 30!)

Speed Comparison Of SELECT Statements
I've searched all over for this and not found that much useful
information for what I think to be a fairly obvious issue.

I am having to do some benchmark tests on a SELECT statement and
whether it will run faster as a massive JOIN or as a series of smaller
SELECTs

i.e.
SELECT *
FROM tbl_1
LEFT OUTER JOIN tbl_2 ON tbl_1.ID=tbl_2.ID
LEFT OUTER JOIN tbl_3 ON tbl_1.ID=tbl_3.ID
LEFT OUTER JOIN tbl_4 ON tbl_1.ID=tbl_4.ID
LEFT OUTER JOIN tbl_5 ON tbl_1.ID=tbl_5.ID
WHERE tbl_1.ID IN (1,2,3,4,5...)

or

SELECT * FROM tbl_1 WHERE ID IN (1,2,3,4,5...)
SELECT * FROM tbl_2 WHERE ID IN (1,2,3,4,5...)
SELECT * FROM tbl_3 WHERE ID IN (1,2,3,4,5...)
SELECT * FROM tbl_4 WHERE ID IN (1,2,3,4,5...)
SELECT * FROM tbl_5 WHERE ID IN (1,2,3,4,5...)

I've written a script that performs one or the other of the statements,
logs the time and outputs the results.

The problem is that the more you run the tests the faster they get

I've tried using FLUSH TABLES and setting the query cache to 0 but
nothing seems to work, the query times keep dropping.

Speed Comparison Between Entering Entries By PHP And Using Another Tbl
I have a table with 100,000 address entries associated with other info, such as resident names, and I have a text file with the lat/long coordinates for those addresses.

So I want to put that lat/long data into the address table. Is it faster to create a table using the text file, then running an update query using those two tables...Or is it quicker to write a php script that enters the lat/long data?

Comma Seperated List Comparison
is it possible to compare acomma separated list aginst another:

eg comma list 1 => 1,2,3,4,5
comma list 2 => 3,5

can you check that 3 is in both, and 5 is in both, therfore they match???

the comparison is to check that if product a who supplies products 1,2,3,4,5
can be used instead of product b who supplies 3,5 as product a already
supplies them.

Good TEXT Comparison Function
I have TEXT type (with returns and line breaks) data in my database, such as:

Ceramic Square Stripes Pot S, 2 col asst
14x14xH11.5cm
Weight: 0.8kg
Blue, Pink
2/16/0.056CBM (63x33x27cm)
G.W.15kgs N.W.13kgs
1x20=8,000pcs
1x40=16,000pcs
1x40HQ=19,520pcs

Many of the times, the text is the same is the same. But I'm looking for a function that can compare two TEXT data and tell me if they're different.