• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 579
  • Last Modified:

sql query string differences?

One field of a MySQL table is a string, each character of which is significant.
For example, the string might be: "XftR".
At times, a new entry will be added, for example, "xftR".

Is there a SQL query that can identify and retrieve the differences?
In the example, the result of such a query would be "X" has changed to "x".
The table structure cannot be modified.
2 Solutions
Chris HarteThaumaturgeCommented:
Use of the keyword binary to make a case sensitive query. This is from the mysql site


The following two statements illustrate that string comparisons are not case sensitive unless one of the operands is a binary string:

mysql> SELECT 'abc' LIKE 'ABC';
        -> 1
mysql> SELECT 'abc' LIKE BINARY 'ABC';
        -> 0
Ray PaseurCommented:
You might want to SELECT myColumn ... WHERE myColumn = 'XFTR' and see if the number of rows was more than one.  Then use an iterator to aggregate all of the column values into an array, using the row id number as the array key and the value in myColumn as the value.  When you have done that you will be able to use array_unique() to see if there are case-sensitive variants on the string in myColumn.

Not sure I understand why a table structure could not be modified.  ALTER TABLE is a fairly common MySQL command.  But I also don't think it is needed to do what you want.
Brad HoweCommented:
You could do something like this.

SELECT stringname, (stringname NOT LIKE '%xftR') as Match where Match = 1 FROM yourtable;

This will give you the output of all values that DONT MATCH your
XftR    0
xFTr    0

Then do an UPDATE WHERE (SELECT ...).

I don't have any dummy data to test but the theory is there.

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

pillmillAuthor Commented:
Thanks. Actually, the problem is a little more complex:

The table structure:

id     string        status     time
1      abc            0             .
1      Abc            0             .  
1      ABc            0             .      
1      ABC            1            .

I want to retrieve the string with status=1 and then all
of the changes that may have occurred in the first, second and third
character of the string. I do not know what the string is
in advance.


This should do what you just said, assuming string is always 3 characters:

SELECT string FROM yourtable WHERE status=1
SELECT string FROM yourtable WHERE string<>(SELECT string FROM yourtable WHERE status=1)

It would be easiest to find the specific changes client-side, once you get the list of differing strings.

Kevin CrossChief Technology OfficerCommented:
You can use case sensitive (cs) collation:
Kevin CrossChief Technology OfficerCommented:
So to be clear, you can set your column (or table or even database) to be case sensitive like latin1_general_cs or you can do this at query time using the COLLATE operator.

SELECT * FROM your_table WHERE your_column COLLATE latin1_general_cs = 'XftR';

If you set the column properly, then you only have to do this:

SELECT * FROM your_table WHERE your_column = 'XftR';


Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now