asked on

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?

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.

mrh14852

ASKER

How would I use LIKE in a >= situation?

Kevin Cross

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?

mrh14852

ASKER

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 %%%?

ASKER CERTIFIED SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mrh14852

ASKER

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.