UDF Library: 'nthValue'

Component: 'nthValue.c'

/*
   Function: nthValue
   Author: Robert D. Meunier - Database Fusion
   Version: 1.0

   Purpose: To extract the Nth value from a delimited list of values.

   Syntax: nthValue( InputString, Delimiter[*], NthPosition)
   Where:  InputString:  A SQL VarChar datatype containing the list of
                         delimited values.
   Delimiter:  The character that is used to delimit the list.
               The delimiter must be a single character followed
               by an optional asterisk. The asterisk says to treat
               multiple back to back delimiters as a single
               delimiter.
   NthPosition:  The value from within the delimited list that
                 is to be returned.
   Returns: A SQL VarChar datatype containing the requested postitional
            value from the list of values. If a NULL InputString is
            Supplied NULL will be returned. If NthPosition is out of range
            a zero length string will be returned.
*/

/* How to create this function through BTEQ:
REPLACE FUNCTION syslib.nthValue( InputString VARCHAR(32767), Delimiter VARCHAR(2), NthPosition INTEGER )
           RETURNS VARCHAR(1024)
           LANGUAGE C
           NO SQL
           PARAMETER STYLE SQL
           CALLED ON NULL INPUT
           EXTERNAL NAME 'SS!nthValue!nthValue.c!F!nthValue'
;
*/

#define SQL_TEXT Latin_Text
#include "sqltypes_td.h"
#include <string.h>
#define EOS '\0'
#define IsNULL -1
#define IsNotNULL 0
#define NoSQLError "00000"
#define NoSQLErrorStr ""
/* MAX_RESULT needs to be set to the VARCHAR lenght of the RETURNS parameter */
/* in the REPOLACE FUNCTION statement. */
#define MAX_RESULT 1024

void nthValue(  VARCHAR_LATIN *inputStr
              , VARCHAR_LATIN *delim
              , INTEGER *nthPos
              , VARCHAR_LATIN *nthValue
              , int *inputStrInd
              , int *delimInd
              , int *nthPosInd
              , int *nthValueInd
              , char SQLState[6]
              , SQL_TEXT extName[129]
              , SQL_TEXT specificName[129]
              , SQL_TEXT errorMessage[257]
              )
{
  /* Define variables */
  int delimLen;
  int curPos=1;
  int resultLen=0;

  /* Initialize some stuff... */
  *nthValue = '\0';
  *nthValueInd = IsNotNULL;
  strcpy(SQLState, NoSQLError);
  strcpy((char *) errorMessage, NoSQLErrorStr);

  /* If input is NULL return NULL */
  if ( *inputStrInd == IsNULL ) {
       *nthValueInd = IsNULL;
       return;
  }

  /* Validate the parameter "Delimiter" */
  if ( *delimInd == IsNULL ) {
       strcpy(SQLState, "U0011");
       strcpy((char *) errorMessage, "Parameter \"Delimiter\" can not be null.");
       return;
  }
  else {
         delimLen = (int) strlen((const char *)delim);
         if ( delimLen != 1 ) {
              if ( delimLen == 2 && delim[1] == '*') {
                   /* No-op */
                 }
              else {
                     strcpy(SQLState, "U0012");
                     strcpy((char *) errorMessage,
                          "Parameter \"Delimiter\" should by one character followed by an optional asterisk.");
                     return;
                   }
            }
  }

  /* Validate the parameter "nth Position" */
  if ( *nthPosInd == IsNULL ) {
       strcpy(SQLState, "U0021");
       strcpy((char *) errorMessage, "Parameter \"Nth Position\" can not be null.");
       return;
  }

  /* Loop throught the input string until:
       1) reach end-of-string
       2) get past the requested value */
  for (; *inputStr != EOS && curPos <= *nthPos && resultLen < MAX_RESULT ; inputStr++) {
         /* Look for the delimiter */
         if ( *inputStr == *delim ) {
              curPos++;
              if ( delim[1] == '*' ) {
                   /* If "*" is specified, loop throught the input string until:
                         1) reach end-of-string
                         2) get past the requested value
                         3) the current value is no longer the delimiter */
                   while ( *inputStr != EOS && curPos <= *nthPos && *inputStr == *delim ) {
                           inputStr++;
                         }
                   inputStr--; /* back up to the last delimiter character */
                 }
            }
         /* If this is the Nth value in the list, building the return string */
            else if ( curPos == *nthPos ) {
                      strncat((char *)nthValue, (const char *)inputStr, (size_t )1);
                      resultLen++;
                    }
  }
  return;
}