Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2013
    Posts
    8

    Unanswered: How can i use details from another table within xmlquery fn:matches statement

    All,

    As a newbie i was very impressed with the response to my last post, so where better to try for some more information.

    Follwing the good advice from my last visit i am using the following query to select information from a table using regular expressions

    SELECT *
    FROM BankDetails
    WHERE xmlcast(xmlquery('fn:matches($s, "^[a-zA-Z]{2}[0-9]{2}")' passing BankAccount AS "s") AS int)=1
    AND BankAccount not like ('IBAN%')
    AND CURRENCY = 'EUR'

    Having looked at the data within our database, i need to be more specific in the regualr expersion. Instead of seeing if the first two characters are just alphanumeric, i need to see if the first two letters are a valid ISO Country code, i.e. NL, GB, IT, IE.

    We have a list of ISO Country codes within another table, therefore, i was hoping to use this data within the select statement. My thinking was:-

    Select * From BankDetails Where ISOCode xmlquery..... in (select ISOCode from Country) - This example is not syntax correct, but hopefully gets my idea across.

    Would anyone have any ideas? I think that i need to maintain the regular expresion and i want to the select records which have the following format nn00%, where nn is the ISO Code and 00 is any number and % denotes that this will be the prefix, a crude determination of a IBAN for bank code

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Wouldn't this work: "where substr(BackAccount,1,2) in (select ISOCode from Country)"?

    Andy

  3. #3
    Join Date
    Jul 2013
    Posts
    8
    Andy,

    Thank you, it has worked with a little tweek. I also need to ensure that the characters 3 and 4 where numbers so i have ended up with the following:-

    SELECT *
    FROM BankDetails
    WHERE substr(BANKACCOUNT,1,2) in (select ISOCode from Country)
    AND xmlcast(xmlquery('fn:matches($s, "^[a-zA-Z]{2}[0-9]{2}")' passing BankAccount AS "s") AS int)=1

Posting Permissions

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