sponsored links

Collapse

Announcement

Collapse
No announcement yet.

Regexp_like

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Regexp_like

    We are on OS7.2 and I have a simple SQL that looks like this:

    Code:
    Select * From lib/file
    Where field1 Like '%xx'
    And Date(field2) >= 'yyyy-mm-dd';
    I'd like to replace the like '%xx' clause with a regular expression:

    Code:
    Select * From lib/file
    Where Regexp_Like(field1, '[x-y][x-y]$')
    And Date(field2) >= 'yyyy-mm-dd';
    However, this causes an error:
    SQL0181
    SQL State 22007.
    Vendor Code -181
    Message: Value in date, time or timestamp string not valid.

    Note that yyyy-mm-dd is a real date (e.g. 2020-09-11).
    field1 is a character field.
    field2 is a timestamp.
    Regular expressions are very foreign to me however I can't understand why changing the Like clause to a regexp_like is causing an issue with the Date(field2) condition? I'm sure it's something daft I'm doing...
    Last edited by john.sev99; September 10th, 2020, 08:14 PM.

  • #2
    Found the issue. It was due to some bad dates in field2. Odd it only showed up when I used a regexp though.

    Comment


    • #3
      With the original you are looking for field1 values where the last 2 characters are both x. But with the regex version, you are looking for x or y. So it will match more records

      Comment

      Working...
      X