Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Nov 2011
    Posts
    8

    Unanswered: Pattern Matching Problem in DB2

    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
    Last edited by eugenebalt; 11-29-11 at 15:33.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

  3. #3
    Join Date
    Nov 2011
    Posts
    8
    Quote Originally Posted by n_i View Post
    All right, I updated my post.

    DB2 v9.5.400.576

    Windows XP

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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"

  5. #5
    Join Date
    Nov 2011
    Posts
    8
    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.

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by ARWinner View Post
    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. http://www.dbforums.com/db2/1672143-...y-install.html

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

  8. #8
    Join Date
    Nov 2011
    Posts
    8
    I found REVERSE in our function library. Thanks

  9. #9
    Join Date
    Nov 2011
    Posts
    8
    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?

  10. #10
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Another option: "...where right('new.net',length(rtrim(s.suffix))) = rtrim(s.suffix)"

  11. #11
    Join Date
    Nov 2011
    Posts
    8
    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.

  12. #12
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    well, clearly you need to handle the cases where the suffix is longer than the string that is being matched.

  13. #13
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  14. #14
    Join Date
    Nov 2011
    Posts
    8
    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.

  15. #15
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    But that didn't work.
    You didn't learn nothing from your experience.

    n_i wrote in this thread
    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).
    Last edited by tonkuma; 11-30-11 at 12:48. Reason: Add last two more concrete paragraphs.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •