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

10.20.111.04.040
10.20.123.04.040
10.20.222.04.112
20.40.555.06.566
5555.44.333
4.45.76.22

In tableB the field will look like
10.20.???.04.040
10.20.???.04.112
20.40.???.06.566
5555.44.333
4.45.76.22

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

So the compare would be something like

CASE WHEN 10.20.111.04.040 >= 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?

0
mrh14852
Asked:
mrh14852
  • 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.
0
 
mrh14852Author Commented:
How would I use LIKE in a >= situation?
0
 
Kevin CrossChief Technology OfficerCommented:
Okay. What are you trying to do really? >= against strings will result in '4.45.76.22' >= '10.20.???.04.040' -- is that what you want?
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
mrh14852Author Commented:
Take this senario for example.

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

Open in new window


The result will be A when it should be B.  How would I use wildcards in place of %%%?
0
 
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.
0
 
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.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

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