• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 583
  • 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 HoweDevOps ManagerCommented:
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.

Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

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';

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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