mysql - trouble with comparing strings

Posted on 2011-10-14
Last Modified: 2012-05-12
I have 2 tables that I an doing a join and as part of that I need to compare 2 strings from both tables and create a red, yellow, green indication but I am stuck.

So I am comparing strings that look like this.

In tableA the filed may look like.

In tableB the field will look like

So as you can see some will have ??? and some will be exact.

So the compare would be something like

CASE WHEN >= 10.20.???.04.040 THEN 'Something'

I need to ignore the .111. and .???. to get the correct match.

I have tried to replace the ??? with %%% i.e.  REPLACE(xxx,'???',%%%')

I can't really use SUBSTRING since I have other numbers in different values...

Whatcha think?

Question by:mrh14852
    LVL 59

    Expert Comment

    by:Kevin Cross
    Try with LIKE or RLIKE/REGEXP. You can use the REPLACE like you showed, but I would go for the single character wildcard or using [0-9] regular expression representation of a single numeric character.
    LVL 8

    Author Comment

    How would I use LIKE in a >= situation?
    LVL 59

    Expert Comment

    by:Kevin Cross
    Okay. What are you trying to do really? >= against strings will result in '' >= '10.20.???.04.040' -- is that what you want?
    LVL 8

    Author Comment

    Take this senario for example.

    WHEN >= 02.11.???.33.444 THEN 'A'
    WHEN BETWEEN 02.11.???.33.555 AND 02.11.???.33.333 THEN 'B'
    WHEN < 02.11.???.33.333 THEN 'C'

    Open in new window

    The result will be A when it should be B.  How would I use wildcards in place of %%%?
    LVL 59

    Accepted Solution

    Can you give the table structures then and more representative sample data from both in the form of INSERT statements. That way, I can test this out. Not sure where you are getting the third and fourth value from to use in the CASE statement since you showed one column per table and BETWEEN goes from lower to higher value, so not sure you will get that to work. Additionally, 444 is between 333, so really need to understand the goal is of the comparison.

    You can use wildcards only when using LIKE or regular expression. Normal comparative operators will require definitive characters. One other way I can think of is to find the index of '???' and replace that same set of characters in the other string ... the risk is if you have one shorter than the other, this may result in an error or invalid results. If you are only ever comparing the end part, then you can take the RIGHT() characters after last '.' -- just depends on the ultimate goal. So along with the sample, it would be good to see what the expected results by row is.
    LVL 8

    Author Comment

    I was able to do by setting up a CASE using LIKE and the format of that field for the different types of formats.

    For example.

    CASE WHEN tableB.fieldA LIKE '%%.%%.%%%.%.%%%' THEN ............

    Thanks for helping me reason through it.

    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

    Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (, A SQLite Tidbit: Quick Numbers Table Generation (…
    All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    728 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

    18 Experts available now in Live!

    Get 1:1 Help Now