SQL Function: Get_Nth_Element - Return the nth Element in a String

Get_Nth_Element.txt

-- SQL Function: Get_Nth_Element - Return the nth Element in a String
-- Parameters: ParString    = String to be searched and split
--             ParDelimiter = Delimiter for splitting into Elements
--             ParElement   = the position of the Element to be returned
Create Function YourSchema/Get_Nth_Element (
         ParString    Clob(1M), 
         ParDelimiter Varchar(10) Default ';',
         ParElement   Integer     Default 1)
   Returns Varchar(4096)
   Language SQL
   Modifies SQL Data
   Concurrent Access Resolution Default
   Fenced
   Deterministic
   Called On Null Input
   External Action Not
   Secured
   Set Option Dbgview = *Source
   Begin
     Declare RtnString  VarChar(4096);
     Declare Continue Handler For SQLEXCEPTION Return 'ERROR';
     
     Set RtnString = 
         (With x (String) as (Values(ParString))
          Select Cast(Element as VarChar(4096))
             from x cross join Table(Systools.Split(ParString, 
                                                    ParDelimiter)) y
             Where Ordinal_Position = ParElement);
     Return RtnString;  
   End;

Label On Routine HSCOMMON05/Get_Nth_Element(Clob(), Varchar(), Int)
   Is 'Get nth Element from String';

Comment On Parameter Routine HSCOMMON05/Get_Nth_Element (Clob(), Varchar(), Int)
   (ParString    Is 'String to be searched', 
    ParDelimiter Is 'Delimiter',
    ParElement   Is 'Elemen to be returned');