Datacom SQL LOCATE_IN_STRING

Products

• Datacom
• DATACOM - AD

Issue/Introduction

CA Datacom SQL Version 15.1 provides a new character scalar function LOCATE_IN_STRING.

Environment

z/IS

Resolution

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