Results 1 to 10 of 10
  1. #1
    Join Date
    May 2011
    Posts
    11

    Unanswered: How To Find String In nth Occurance?

    Hello,
    I am wondering is there a DB2 string function to search for "hello" in the 3rd occurance? I'm hoping there's system function already so I don't have to rewrite the loop.

    Your help is appreciated!

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I am fairly certain there is no such function, but then I do not know what you want exactly. Maybe if you gave us some examples of what you have and what you want to get, that would help.

    Andy

  3. #3
    Join Date
    May 2011
    Posts
    11
    Str='usa:ca:oakland:98832'
    Str='cym:no region:george town:0'
    Str='ca:no region:vancouver:0'

    I want to return the following
    'oakland'
    'george town'
    'vancouver'

    I was hoping there's nth occurance string function so I can plug in ':' and return exact string between 3rd & 4th occurances.

    Does my example help?
    Last edited by sathyaram_s; 08-31-11 at 18:01. Reason: disabled similies in text

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    LOCATE_IN_STRING(INSTR is a synonym) function is supported on DB2 9.7 for LUW.

    INSTRB user-defined-function is in here Sample UDFs for Migration

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Str='usa:ca:oakland:98832'
    Str='cym:no region:george town:0'
    Str='ca:no region:vancouver:0'

    I want to return the following
    'oakland'
    'george town'
    'vancouver'
    I thought that you got string between 2nd & 3rd occurances of ':' in the string.


    It is not neccesary to use loop, if you nested the LOCATE functions.

    Example 1:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     sample_data(str) AS (
    VALUES
      'usa:ca:oakland:98832'
    , 'cym:no region:george town:0'
    , 'ca:no region:vancouver:0'
    )
    SELECT str
         , SUBSTR(  str
                  , LOCATE( ':' , str , LOCATE( ':' , str ) + 1 ) + 1
                  , LOCATE( ':' , str , LOCATE( ':' , str , LOCATE( ':' , str ) + 1 ) + 1 )
                  - LOCATE( ':' , str , LOCATE( ':' , str ) + 1 ) - 1
                 ) AS extracted
     FROM  sample_data
    ;
    ------------------------------------------------------------------------------
    
    STR                         EXTRACTED                  
    --------------------------- ---------------------------
    usa:ca:oakland:98832        oakland                    
    cym:no region:george town:0 george town                
    ca:no region:vancouver:0    vancouver                  
    
      3 record(s) selected.
    Example 2: Remove redundant code by using an nested-table-expression.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     sample_data(str) AS (
    VALUES
      'usa:ca:oakland:98832'
    , 'cym:no region:george town:0'
    , 'ca:no region:vancouver:0'
    )
    SELECT str
         , SUBSTR( str
                 , second + 1
                 , LOCATE( ':' , str , second + 1 ) - second - 1
                 ) AS extracted
     FROM  (SELECT str
                 , LOCATE( ':' , str , LOCATE( ':' , str ) + 1 ) AS second
             FROM  sample_data
           ) s
    ;
    ------------------------------------------------------------------------------
    
    STR                         EXTRACTED                  
    --------------------------- ---------------------------
    usa:ca:oakland:98832        oakland                    
    cym:no region:george town:0 george town                
    ca:no region:vancouver:0    vancouver                  
    
      3 record(s) selected.
    Last edited by tonkuma; 08-31-11 at 19:27. Reason: Remove " || ':' " in Example 2.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Example 3: Used a (scalar-fullselect).
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     sample_data(str) AS (
    VALUES
      'usa:ca:oakland:98832'
    , 'cym:no region:george town:0'
    , 'ca:no region:vancouver:0'
    )
    SELECT str
         , (SELECT SUBSTR( str
                         , second + 1
                         , LOCATE( ':' , str , second + 1 ) - second - 1
                         )
             FROM  LATERAL
                   (VALUES LOCATE( ':' , str , LOCATE( ':' , str ) + 1 )
                   ) AS s(second)
           ) AS extracted
     FROM  sample_data
    ;
    ------------------------------------------------------------------------------
    
    STR                         EXTRACTED                  
    --------------------------- ---------------------------
    usa:ca:oakland:98832        oakland                    
    cym:no region:george town:0 george town                
    ca:no region:vancouver:0    vancouver                  
    
      3 record(s) selected.
    Last edited by tonkuma; 08-31-11 at 19:27.

  7. #7
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    For those that are interested, in DB2 V9.7, the LOCATE_IN_STRING function actually as a parameter that allows you to ask for location of the nth occurrence of a string.

    LOCATE_IN_STRING( source-string, search-string, start, instance)

    where instance is the nth instance of the search-string in source-string from start.

    I am not saying this is any better that the examples Tonkuma has already shown, just that it is possible if anyone ever needs it:
    Code:
    WITH
     sample_data(str) AS (
    VALUES
      'usa:ca:oakland:98832'
    , 'cym:no region:george town:0'
    , 'ca:no region:vancouver:0'
    )
    SELECT STR
         , SUBSTR(  STR
                 , (LOCATE_IN_STRING(STR,':',1,2) + 1)
                 , (LOCATE_IN_STRING(STR,':',1,3)    ) - (LOCATE_IN_STRING(STR,':',1,2) + 1)
                 ) AS EXTRACTED
    FROM SAMPLE_DATA
    ;
    STR                         EXTRACTED                  
    --------------------------- ---------------------------
    usa:ca:oakland:98832        oakland                    
    cym:no region:george town:0 george town                
    ca:no region:vancouver:0    vancouver
    The second parameter of the SUBSTR finds the second occurrence of ':' plus one for an offset.
    The third parameter subtracts the position of the second occurrence of ':' plus one for an offset from the position of the third occurrence of ':' to get the length for the SUBSTR.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another example using LOCATE_IN_STRING(synonym is INSTR)

    Code:
    WITH
     sample_data(str) AS (
    VALUES
      'usa:ca:oakland:98832'
    , 'cym:no region:george town:0'
    , 'ca:no region:vancouver:0'
    )
    SELECT str
         , SUBSTR( LEFT( str , LOCATE_IN_STRING(str , ':' , 1 , 3) - 1 )
                 , LOCATE_IN_STRING(str , ':' , 1 , 2) + 1
                 ) AS extracted
     FROM  sample_data
    ;
    ------------------------------------------------------------------------------
    
    STR                         EXTRACTED                  
    --------------------------- ---------------------------
    usa:ca:oakland:98832        oakland                    
    cym:no region:george town:0 george town                
    ca:no region:vancouver:0    vancouver

    STR
    usa:ca:oakland:98832

    LEFT( str , LOCATE_IN_STRING(... , 3) - 1 )
    usa:ca:oakland

    SUBSTR( LEFT(...) , LOCATE_IN_STRING(... , 2) + 1 )
    usa:ca:oakland
    Last edited by tonkuma; 09-04-11 at 07:57.

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another example without using LOCATE_IN_STRING.

    Example 4:
    Code:
    WITH
     sample_data(str) AS (
    VALUES
      'usa:ca:oakland:98832'
    , 'cym:no region:george town:0'
    , 'ca:no region:vancouver:0'
    )
    SELECT str
         , SUBSTR( str , sec_plus , LOCATE(':' , str , sec_plus) - sec_plus ) AS extracted
     FROM  sample_data
         , LATERAL
           (VALUES LOCATE( ':' , str , LOCATE( ':' , str ) + 1 ) + 1 ) AS s(sec_plus)
    ;
    ------------------------------------------------------------------------------
    
    STR                         EXTRACTED                  
    --------------------------- ---------------------------
    usa:ca:oakland:98832        oakland                    
    cym:no region:george town:0 george town                
    ca:no region:vancouver:0    vancouver
    "," used to join in the from clause can be replaced by "CROSS JOIN", like...
    Code:
    ...
     FROM  sample_data
     CROSS JOIN
           LATERAL
           (VALUES LOCATE( ':' , str , LOCATE( ':' , str ) + 1 ) + 1 ) AS s(sec_plus)
    Comparing it with example 2,
    you would see an alternate syntax to define variables(exactly columns) for common expressions in a query, like...
    , LATERAL(VALUES (... [, ...]) ) [AS] s(... [, ...])
    or
    CROSS JOIN LATERAL(VALUES (... [, ...]) ) [AS] s(... [, ...])

  10. #10
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Another example without using LOCATE_IN_STRING, but this time parametrised on n, i.e., the following query works also for finding (say) the 27th field in the given column by just replacing "3" by "27":
    Code:
    WITH
     sample_data(str) AS (
    VALUES
      'usa:ca:oakland:98832'
    , 'cym:no region:george town:0'
    , 'ca:no region:vancouver:0'
    )
    ,
    pieces(full, nth, rest, n, pos) AS (
      SELECT str, str, str, 0, locate(':',str) FROM sample_data
     UNION ALL
      SELECT full, CASE WHEN pos>0 THEN substr(rest,1,pos-1) ELSE rest END,
                   CASE WHEN pos>0 THEN substr(rest,pos+1) ELSE ':' END,
                   n+1, locate(':',substr(rest,pos+1))
        FROM pieces WHERE rest <> ':'
    )
    SELECT full,nth FROM pieces WHERE n=3
    --                                ^^^^ replace this 3 by whatever you want
    ;
    The first CTE is of course for the table content (so remove it when working with a "real" table) while the second CTE is to be kept (replace the "," by "WITH");
    it's a recursive CTE.
    Last edited by Peter.Vanroose; 09-04-11 at 19:50.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

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
  •