How To Find String In nth Occurance?

captainlaw · #1 (**permalink**) 08-31-11, 16:20

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!

ARWinner · #2 (**permalink**) 08-31-11, 16:44

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

captainlaw · #3 (**permalink**) 08-31-11, 16:59

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?

tonkuma · #4 (**permalink**) 08-31-11, 17:14

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

tonkuma · #5 (**permalink**) 08-31-11, 17:57

Quote:

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.

tonkuma · #6 (**permalink**) 08-31-11, 18:15

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.

Stealth_DBA · #7 (**permalink**) 08-31-11, 20:22

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.

tonkuma · #8 (**permalink**) 09-04-11, 06:49

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

tonkuma · #9 (**permalink**) 09-04-11, 11:02

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(... [, ...])

Peter.Vanroose · #10 (**permalink**) 09-04-11, 18:47

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech