ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Reverse Locate

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

  • Reverse Locate

    HI all,

    is there a DB2 Built in funcion that made a locate reverse, like a %Scanr of the Rpg.

    Many thanks.
    Bye

  • #2
    So - just to be sure, you're wanting to find the last occurrence of a string - that is 'abcdefcd' and you're looking for 'cd' you want 7 rather than 3?

    I think you'd have to to write a SQL Function to accomplish this. If you want I can probably help you with that.

    Comment


    • #3
      There are 2 built-in Db2 functions that can do this: INSTR and LOCATE_IN_STRING. The trick is to specify the starting position as -1 . The -1 instructs the function to search from the end of the string to the beginning (reverse search). Both will get the job done and allow you to specify which instance of the string to search for.

      The below example (with some tweaking) is taken from the IBM Db2 for i documentation:

      SELECT LOCATE_IN_STRING('WINNING', 'N', -1, 3), LOCATE_IN_STRING('WINNING', 'N', -1), INSTR('WINNING', 'N', -1, 2), INSTR('WINNING', 'N', -1)
      FROM SYSIBM.SYSDUMMY1;


      Comment


      • RDKells
        RDKells commented
        Editing a comment
        Aww herb, beat me to it.

      • paolinosal
        paolinosal commented
        Editing a comment
        Excuse me Herb .. I didn't see your post!
        Many thanks too.
        Bye

      • Herb...
        Herb... commented
        Editing a comment
        That's ok. We're just helping each other. It's all good.

    • #4
      LOCATE_IN_STRING

      "If the value of the integer is greater than zero, the search begins at start and continues for each position to the end of the string. If the value of the integer is less than zero, the search begins at the LENGTH(source-string) + start + 1 and continues for each position to the beginning of the string."

      Code:
      CREATE TABLE RAKELLR/TEST
      (FIELD1 CHAR (30 ) NOT NULL WITH DEFAULT)
      
      INSERT INTO RAKELLR/TEST      
      VALUES 'TEST STRING HERE TEST'
      
      SELECT LOCATE_IN_STRING(FIELD1,'TEST', -1)
        FROM RAKELLR/TEST                       
      
      LOCATE_IN_STRING               
                   18                
      ********  End of data  ********

      Comment


      • #5
        Hi,

        Many thanks RDKells it works.
        what I want to do is monitoring strange connections to our server:

        Code:
        Select Remote_Address, Locate_in_String(Remote_Address, '.' , -1), Local_Port
         From Qsys2.Netstat_Info
         Where Tcp_State = 'SYN-RECEIVED';
        Now it works.
        Grazie

        Comment

        Working...
        X