Pattern Matching Problem in DB2

eugenebalt · #1 (**permalink**) 11-29-11, 13:57

I have the following problem in DB2.

Let's say I have a table, called SUFFIXES, with 3 rows:

- com
- gov
- test.net

I need a SQL statement that for any arbitrary string str, will tell me if there exists at least 1 row in SUFFIXES, r, such that str LIKE %r (in other words, ends with any row in the table).

Examples:

test.com -> Matches "com"
test.net -> Matches "test.net"
new.net -> does NOT match

I tried this, but it didn't work:

select * from SUFFIXES s where 'test.com' LIKE '%' || s.SUFFIX

Thanks

Versions: DB2: 9.5.400 Windows: XP

n_i · #2 (**permalink**) 11-29-11, 14:01

Let's start with this: Must Read before posting

eugenebalt · #3 (**permalink**) 11-29-11, 14:34

Quote:

Originally Posted by n_i

Let's start with this: Must Read before posting

All right, I updated my post.

DB2 v9.5.400.576

Windows XP

n_i · #4 (**permalink**) 11-29-11, 15:25

I was more interested in "it didn't work" part. Is your computer switched on?

Or try "...where locate(s.suffix, 'test.com') = length('test.com') - length(s.suffix) + 1"

eugenebalt · #5 (**permalink**) 11-29-11, 15:31

Thanks but that didn't work. If I do

where locate(s.suffix, 'new.net') = length('new.net') - length(s.suffix) + 1

I still get results, and I shouldn't. 'new.net' should not match because I don't have anything in my table which is 'new.net' LIKE %row.

By the way, lengths don't matter. I could have an ending of any length, not just the 3-letter suffix, and it should still get checked. We can't rely on lengths to give the correct result.

Yes, my computer is switched on right now, I am ready to try any solutions.

ARWinner · #6 (**permalink**) 11-29-11, 16:30

There is a problem with this: locate(s.suffix, 'new.net') = length('new.net') - length(s.suffix) + 1

It needs to be: locate(s.suffix, 'new.net') = length('new.net') - length(s.suffix) + 1 and locate(s.suffix, 'new.net') <> 0

But this still has a problem, if the string occurs more than once that it will fail even if it ends with the string.

What you will need is a UDF that reverses a string (UDF_REVERSE). Then this should work:

locate(UDF_REVERSE(s.suffix),(UDF_REVERSE('new.net ')) = 1

Andy

n_i · #7 (**permalink**) 11-29-11, 16:45

Quote:

Originally Posted by ARWinner

There is a problem with this: locate(s.suffix, 'new.net') = length('new.net') - length(s.suffix) + 1

True. I was hoping eugenebalt would get the idea though.

There are many other ways to skin this cat, e.g. Regular Expression Functions (UDF) can easily install

If we're resorting to UDFs we might as well use java.util.regexp.Pattern.

eugenebalt · #8 (**permalink**) 11-29-11, 16:53

I found REVERSE in our function library. Thanks

eugenebalt · #9 (**permalink**) 11-29-11, 16:59

ARWinner, small typo, you missed a closing paren.

locate(REVERSE(s.suffix),(REVERSE('new.net '))) = 1

Thanks though, works. Can you explain why it works? the logic?

n_i · #10 (**permalink**) 11-29-11, 17:07

Another option: "...where right('new.net',length(rtrim(s.suffix))) = rtrim(s.suffix)"

eugenebalt · #11 (**permalink**) 11-29-11, 17:12

The last suggestion did not work. ARWinner's with REVERSE did, though, good job.

With the last suggestion,

where right('new.net',length(rtrim(s.suffix))) = rtrim(s.suffix)

----------- -------------------------- ------------------------------
SQL0138N A numeric argument of a built-in string function is out of range.
SQLSTATE=22011

SQL0138N A numeric argument of a built-in string function is out of range.

n_i · #12 (**permalink**) 11-29-11, 17:30

well, clearly you need to handle the cases where the suffix is longer than the string that is being matched.

tonkuma · #13 (**permalink**) 11-29-11, 21:33

How about this?

Example 1:

Code:

------------------------------ Commands Entered ------------------------------
WITH
 suffixes(suffix) AS (
VALUES
  'com'
, 'gov'
, 'test.net'
)
, test_string(str) AS (
VALUES
  'test.com'
, 'test.net'
, 'new.net'
, 'goverment-net.gov'
)
SELECT str
     , suffix
 FROM  test_string
 LEFT  OUTER JOIN
       suffixes
   ON  /* str LIKE %suffix */
       SUBSTR(str , MAX(LENGTH(str) - LENGTH(suffix) , 0) + 1) = suffix
    /* str LIKE '%' || suffix -- This works DB2 9.7.4 for LUW or later */
;
------------------------------------------------------------------------------

STR               SUFFIX  
----------------- --------
test.com          com     
test.net          test.net
new.net           -       
goverment-net.gov gov     

  4 record(s) selected.

eugenebalt · #14 (**permalink**) 11-30-11, 10:26

Thanks Tonkuma, I'll try it.
One last question, any idea how to put the REVERSE solution by ARWinner in a Select clause?

I have another table, USERS, with a field called EMAIL_ADR.

I need to select all users from USERS whose EMAIL_ADR satisfies the suffix rows in SUFFIX. We already have the solution on pattern matching.

So I tried this:

select * from USERS u where

((select suffix from SUFFIX s where locate(REVERSE(s.suffix),(REVERSE(u.EMAIL_ADR))) = 1) is not null)

But that didn't work.

tonkuma · #15 (**permalink**) 11-30-11, 11:27

Quote:

But that didn't work.

You didn't learn nothing from your experience.

n_i wrote in this thread

Quote:

I was more interested in "it didn't work" part. Is your computer switched on?

Did you got error message(s)?
If so, please copy and paste the error message(s)
and exact SQL statement you executed.
Because, you used table SUFFIX(in last post) insted of SUFFIXES(in OP).

If the result was different from your expectation,
please write your expected result and the result you got with sample data of both tables(SUFFIXES and USERS).

Internet Services

Web Development

Digital Marketing

Consumer Tech