title: DB2 query to extract data between two delimiters
body: <p>I need to fix a query on a DB2 version that does not support LOCATE_IN_STRING. The query is of a table that lists file space names and the value is always between the 3rd and 4th '\'. The data in the field looks like this.</p>
  
  <pre><code>Unnamed[1]: AMER-AP-DAG\Microsoft Exchange Writer\{76fe1ac4-15f7-4bcd-987e-8e1acb462fb7}\AMER-MB-08\6bc391ef-a370-49a1-8f05-b1bed9-e5ad55
  
  Unnamed[1]: AMER-AP-DAG\Microsoft Exchange Writer\{76fe1ac4-15f7-4bcd-987e-8e1acb462fb7}\Mailbox Database 1303547393\2bc06db2-1966-4fd7-9545-f667102b0b7d
  
  Unnamed[1]: AMER-AP-DAG\Microsoft Exchange Writer\{76fe1ac4-15f7-4bcd-987e-8e1acb462fb7}\APAC-MB-05\11dedbd4-1757-45c8-8991-f0f713-4ef210
  </code></pre>
  
  <p>When I use LOCATE the best I can get is the string I need but it still has all the data after the 4th '\'. Also as you can see the string length can vary but the string always seems to start at character position 78.</p>
  
  <pre><code>Select -                                                                        
   SUBSTR(FILESPACE_NAME, LOCATE('\',FILESPACE_NAME, LOCATE('\',FILESPACE_NAME, - 
   LOCATE('\', FILESPACE_NAME, -                                                  
   LOCATE('\', FILESPACE_NAME)+1)+1)-1)+1) -                                      
   as FSNAME -                                                                    
  
  from filespaces -                                                               
   where node_name='AMER-AP-DAG'                                                  
  </code></pre>
  
  <p>FSNAME: AMER-MB-08\6bc391ef-a370-49a1-8f05-b1bed9e5ad55</p>
  
  <p>FSNAME: Mailbox Database 1143943276\55ac1670-efc9-4301-ac5c-beb5cd2d77cb</p>
  
  <p>FSNAME: APAC-MB-05\11dedbd4-1757-45c8-8991-f0f7134ef210</p>