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

mysql - trouble with comparing strings

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?

  • 3
  • 3
1 Solution
Kevin CrossChief Technology OfficerCommented:
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.
mrh14852Author Commented:
How would I use LIKE in a >= situation?
Kevin CrossChief Technology OfficerCommented:
Okay. What are you trying to do really? >= against strings will result in '' >= '10.20.???.04.040' -- is that what you want?
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

mrh14852Author Commented:
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 %%%?
Kevin CrossChief Technology OfficerCommented:
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.
mrh14852Author Commented:
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.
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

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

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