ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

SQL regex_like() Not Found On v7r3?

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

  • SQL regex_like() Not Found On v7r3?

    According to the following link, I should be able to execute regexp_like() on our system. We are on V7R3 TR 4.

    The REGEXP_LIKE predicate searches for a regular expression pattern in a string.


    How do I determine which V7R3 TR # the regexp functions come with?

    When I run the following:

    Code:
    select regex_like(myfield,'[0-9]{3}-[0-9]{3}-[0-9]{2}'
    from mytable
    where mykey = 'myvalue'
    ..i get the following error..

    Code:
    Message ID . . . . . . :   SQL0204                                            
    
    
    Message . . . . :   REGEX_LIKE in *LIBL type *N not found.                    
    
    Cause . . . . . :   REGEX_LIKE in *LIBL type *N was not found. If the member  
      name is *ALL, the table is not partitioned. If this is an ALTER TABLE       
      statement and the type is *N, a constraint or partition was not found. If   
      this is not an ALTER TABLE statement and the type is *N, a function,        
      procedure, trigger or sequence object was not found.                        
        If a function was not found, REGEX_LIKE is the service program that       
      contains the function.  The function will not be found unless the external  
      name and usage name match exactly.  Examine the job log for a message that  
      gives more details on which function name is being searched for and the name
      that did not match.

  • #2
    You missing the P in regexp_like. Also, I don't think regexp_likeis what you want there, maybe regexp_substr?

    Comment


    • #3
      Originally posted by jtaylor___ View Post
      You missing the P in regexp_like. Also, I don't think regexp_likeis what you want there, maybe regexp_substr?
      Thanks for your response. I originally did include the p but didn't when i went to copy/paste the results.

      Here's what I get:
      Code:
      [COLOR=#C71414]Message: [SQL0204] REGEXP_LIKE in *LIBL type *N not found.
      [/COLOR]

      Comment


      • #4
        You can't use regexp_like where you're trying to use it. I tried it and it gives me the same (slightly misleading) error. Like belongs in a where clause.

        Comment


        • TheZenbudda
          TheZenbudda commented
          Editing a comment
          I was being unintentionally dense. I see you tried to warn me about the clause i was putting the regexp_like in.

      • #5
        regexp_like() returns boolean true or false, which is not a selectable data type. You can use it anywhere a boolean condition is accepted - e.g. a where statement condition, or a case statement condition.
        I like using a case statement to convert regex_like() to 'Y'/'N', or if I'm using embedded SQL in RPGLE, I like converting it to an indicator '0'/'1'
        Code:
        matched = *off;
        exec sql
          select case when regex_like(myfield,'[0-9]{3}-[0-9]{3}-[0-9]{2}')
                      then '1' else '0' end
            into :matched
            from mytable
           where mykey = 'myvalue';
        if matched;
          ...
        endif;
        Regexp search functions that return selectable values directly:
        • regexp_substr() returns the first substring that matches the specified pattern
        • regexp_count() returns the number of matches for this pattern in this string
        • regexp_instr() returns the position of the first match (like %scan() or SQL locate() )

        Comment


        • Vectorspace
          Vectorspace commented
          Editing a comment
          (regexp_like() is a predicate, the rest are scalar functions)
      Working...
      X