Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2016
    Posts
    3

    Unanswered: Filter rows satisfying continuous series of numbers

    Hi,

    I have a requirement to scan through a table column (Alphanumeric) and retrieve rows which satisfy particular series of numbers like all series from 2000 till 2999 , 7000 till 7999 in that column.

    Note the column is Alphanumeric.

    I tried using select col from tab1 where col like '%2___%' or col like '%7___%'
    but this retrieves all records that contains 2 or 7 in that column value.

    Do you have any suggestion?

    Regards,
    Vinodh

  2. #2
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    66
    Provided Answers: 6
    Hi,

    try this:
    Code:
    select *
    from table(values '12345', '23456', '2345a', '71234') t(c)
    where xmlcast(
    xmlquery('fn:matches($s, $p)' passing 
      t.c as "s"
    , '^(2|7)[0-9]+$' as "p"
    )
    as int)=1
    Regards,
    Mark.

  3. #3
    Join Date
    Jul 2016
    Posts
    3
    Hi Mark,

    Thanks for your reply.

    I am trying to fetch the results from Mainframe DB2, so not sure if i can use your below query.
    Can you please advise?

    Regards,
    Vinodh.

    Quote Originally Posted by mark.bb View Post
    Hi,

    try this:
    Code:
    select *
    from table(values '12345', '23456', '2345a', '71234') t(c)
    where xmlcast(
    xmlquery('fn:matches($s, $p)' passing 
      t.c as "s"
    , '^(2|7)[0-9]+$' as "p"
    )
    as int)=1

  4. #4
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    66
    Provided Answers: 6
    Hi Vinodh,

    I'd advise you to specify your db2 version, check an appropriate knowledge center if such a function exists in this version. Moreover, you can even try to run a query like this to verify if it works.

  5. #5
    Join Date
    Jul 2016
    Posts
    3
    Hi Mark,

    We are using DB2 10 for Z/OS. The functions mentioned in your post will not work in the version that i am working currently.

    Regards,
    Vinodh


    Quote Originally Posted by mark.bb View Post
    Hi Vinodh,

    I'd advise you to specify your db2 version, check an appropriate knowledge center if such a function exists in this version. Moreover, you can even try to run a query like this to verify if it works.

  6. #6
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    66
    Provided Answers: 6
    Quote Originally Posted by Vinodh86 View Post
    We are using DB2 10 for Z/OS. The functions mentioned in your post will not work in the version that i am working currently.
    Hi Vinodh,

    According to the link below this function is supported.
    http://www.ibm.com/support/knowledge...fnmatches.html

    I don't have an access to zDB2 at the moment. Can you try to run something like below this as is and provide the result?

    select *
    from (
    Select '12345' from sysibm.sysdummy1 Union all
    Select '23456' from sysibm.sysdummy1 Union all
    Select '2345a' from sysibm.sysdummy1 Union all
    Select '71234' from sysibm.sysdummy1
    ) t(c)
    where xmlcast(
    xmlquery('fn:matches($s, $p)' passing
    t.c as "s"
    , '^(2|7)[0-9]+$' as "p"
    )
    as int)=1
    Regards,
    Mark.

Tags for this Thread

Posting Permissions

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