Datacom SQL LOCATE_IN_STRING

Document ID : KB000113763
Last Modified Date : 28/09/2018
Show Technical Document Details
Introduction:
CA Datacom SQL Version 15.1 provides a new character scalar function LOCATE_IN_STRING.
Environment:
z/IS
Instructions:
Datacom SQL scalar function LOCATE_IN_STRING is a flexible and powerful string search scalar function which has options to:
                                                       
  1. Search for the "nth" occurrence                                         
  2. Search backwards     
                                                   

LOCATE_IN_STRING(source, search, start, instance, units)   
   
                                                                    
For example:
 
Find the domain name in a column named URL in the table named TRANSACTIONS                                   
                                                                           
SELECT URL, SUBSTR(URL, LOCATE_IN_STRING(URL, '.', -1, 2) + 1,             
LOCATE_IN_STRING(URL, '.', -1, 1) -                                        
LOCATE_IN_STRING(URL, '.', -1, 2) - 1) AS DOMAIN                           
FROM TRANSACTIONS;                                                          
 
The result:     

                                                                   
URL                  DOMAIN                                                
-------------------  --------                                             
HTTP:\\WWW.CA.COM    CA                                                    
 
Additional Information:
LOCATE_IN_STRING is an enhancement provided via SQL 15.1 PTF SO05343.
 
Documentation can be found in CA Datacom Core - 15.1 section “Character Functions”.
https://docops.ca.com/ca-datacom/15-1/en/reference/sql-reference/sql-functions/scalar-functions#ScalarFunctions-CharacterFunctions