16.20 - Example: Basic Scalar Function - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
April 2020
Content Type
Programming Reference
Publication ID
B035-1147-162K
Language
English (United States)

Here is an example of a scalar function that extracts a substring from an input string given the starting position of the substring. The scalar function uses parameter style SQL.

/*****  C source file name: substr.c  *****/
#define  SQL_TEXT Latin_Text
#include <sqltypes_td.h>
#include <string.h>
#define  IsNull     -1
#define  IsNotNull  0
void udf_scalar_substr( VARCHAR_LATIN *inputString,
                        INTEGER       *start,
                        VARCHAR_LATIN *result,
                        int           *inputStringIsNull,
                        int           *startIsNull,
                        int           *resultIsNull,
                        char          sqlstate[6],
                        SQL_TEXT      extname[129],
                        SQL_TEXT      specific_name[129],
                        SQL_TEXT      error_message[257] )
{
   int inputStrLength;
   /* Return Null value on Null Input */
   if ((*inputStringIsNull == IsNull))
   {
      strcpy(sqlstate, "22004");
      strcpy((char *) error_message, "Null value not allowed.");
      *resultIsNull = IsNull;
      return;
   }
   strcpy((char *) error_message, " ");
   *resultIsNull = IsNotNull;
   inputStrLength = strlen((const char *) inputString);
   if (*start < 1)
   {
      strcpy((char *) result, (const char *) inputString) ;
      return;
   }
   if (*start > inputStrLength)
   {
      strcpy((char *) result, "");
      return;
   }
   strcpy((char *) result,
          (const char *) inputString + (*start - 1));
}