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.
pillmillAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris Harte2015 Top Expert (Most Article Points)Commented:
Use of the keyword binary to make a case sensitive query. This is from the mysql site

http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html

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:
Hi,
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.

Cheers,
Hades6666
SolarWinds® IP Control Bundle (IPCB)

Combines SolarWinds IP Address Manager and User Device Tracker to help detect IP conflicts, quickly identify affected systems, and help your team take near instantaneous action. Help improve visibility and enhance reliability with SolarWinds IP Control Bundle.

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.

Thanks.

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

SELECT string FROM yourtable WHERE status=1
UNION
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kevin CrossChief Technology OfficerCommented:
You can use case sensitive (cs) collation:
http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html
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';

Kevin
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.