sql query string differences?

Posted on 2011-10-05
Last Modified: 2012-06-21
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.
Question by:pillmill
    LVL 16

    Expert Comment

    by:Chris Harte
    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
    LVL 107

    Expert Comment

    by:Ray Paseur
    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.
    LVL 30

    Expert Comment

    by:Brad Howe
    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.


    Author Comment

    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.


    LVL 13

    Accepted Solution

    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.

    LVL 59

    Expert Comment

    by:Kevin Cross
    You can use case sensitive (cs) collation:
    LVL 59

    Assisted Solution

    by:Kevin Cross
    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

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
    This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
    Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
    The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now