A few basic scalar string UDFs...

Blog
The best minds from Teradata, our partners, and customers blog about whatever takes their fancy.
Enthusiast

Following are some basic scalar UDFs we've written to deal with strings of text. We figured that as frustrating as it must be for us, so is it for everyone else - ergo some code that works.  If you can - please return the favor by posting some of your general solutions to frustrating problems in the Extensibility forum.

These are all well tested on TD13, and should work appropriately on TD12. Download the sources (UDFs.zip) and Standard Documentation (TDStringUDFv3.doc).

Few things to remember:

  1. Many of the scalars return VarChar(4096). This means you can only have a few of them before you blow the row length limit. Often, the first thing you do to the result is to cast it to something smaller, so you don't blow your spool limit (cast (my4Kfunction(arg1,arc2) as Char(20))).  We're still working with the variant data types, but we don't know enough about the behavior to commit to them yet.
  2. We bailed on Unicode. Most of the things we have where Unicode comes across as UTF8 - was encoded in base36 or base64, so we simply don't worry about it much.
  3. Sparse Input / Output documentation in Standard Unix form is a download - look to the right. There may be some delay until they post, since they require review.
  4. We'll try to answer questions in the forums as they come up, support will be as we have time... If you find a bug, same support applies - we are absolutly interested in any you find.
  5. These UDFs are released under the Apache 2.0 License, so there should not be any issue with reuse, though you are required to leave the license header in the sources even if modified.

Available functions

Name Category Description
eCStrChr string Locate the first occurrence of character in string
eCStrCmp string Compare two strings
eCStrCSpn string Get span until the character in string
eCStrNCmp string Compare characters of two strings
eCStrRChr string Locate last occurrence of character in string
eCStrSpn string Get span of character set in string
eCStrStr string Locate substring
eReplaceChar string Replace list of characters with replacement char
eStrBetween string Get the string between two character
eStrReverse string Reverse the string
eStrRight string Get the right of the string
eCollapseWhitespace string Collapse multiple spaces to single space character
eURLDecodeEscapes decode Decodes %XX hex value to characters
eHex2BigInt decode Converts hex string to bigint
eDecodeBase36Vec decode Decodes base36 encoding to bigint
eDecodeHex2BigInt decode Converts encoded hex string to integer
eExtractNVP url Extract value for a name based on name=value& string
eDomainRemoveTLD url Removes Top level domain from the domain name
eGetTopDomain url Gets top level domain from url string
eGetURLDomain url Gets domain name from url string
eGetURLParams url Gets url parameters from url string
eGetURLPath url Gets url path from URL string
eListElementCount list Returns number of elements in the list(delimited string)
eListGetRangeByIdx list Returns the string between the range of delimiters
eListGetValByIdx list Returns a element from list based on index
eListGetLastElement list Returns last element in the list
eListCheckElement list Checks whether element exist in the list
eIsBigInt check Checks whether input string is bigInt
eIsDecimal check Checks whether input string is decimal(m,n)
eIsInteger check Checks whether input string is integer
8 Comments
Senior Supporter
Thanks! Nice list of functions! Will install and test them.

Do you use the functions in your production environment? If yes, in unprotected mode?

Short question to eIsInteger, eIsDecimal, eIsBigInt...
Why not one function which gets a second parameter which specifying which test should be done? Would this be slower?
Enthusiast
If you break out the conversions, it is obvious what they do. From the programming perspective, there is actually less to do, since for the identified function, I do not have to test the option to figure out what code path to follow...

Yes, these functions are being used unprotected in a production environment. We went to a lot of trouble to make these functions only use pointers - they allocate as little storage as possible, either with memory allocation or by using declared variables.
hi. How do i install a teradata UDF function? If installed how can I make some of them available to all users and how do i keep some of them private? Please let me know if you have any guide or link which can provide step by step process
Teradata Employee
@manoj: Take a look at this and see if it helps:

http://developer.teradata.com/extensibility/articles/developing-database-extensions-udfs-etc
Teradata Employee
@manoj: and for community support on UDFs, please visit the Extensibility forum.
Hi guys,

I took a look at these functions and tried to compile them on a teradata express 13 for windows. I encountered the following error when adding the UDFs to my teradata db (ex. running the eStrRight.sql:
Microsoft (R) Program Maintenance Utility Version 9.00.30729.01
Copyright (C) Microsoft Corporation. All rights reserved.

cl /EHsc /D_CRT_SECURE_NO_DEPRECATE /DWIN32 /GS- -D_USE_32BIT_TIME_T /c
/FoeStrRight.obj eStrRight.c
Microsoft (R) 32-bit C/C++ Optimizing Compiler Version 15.00.30729.01 for 8
0x86
Copyright (C) Microsoft Corporation. All rights reserved.

eStrRight.c
eStrRight.c(84) : error C2143: syntax error : missing ';' before 'type'
eStrRight.c(85) : error C2143: syntax error : missing ';' before 'type'

doing a little research it looks like we need to move all variable declarations immediately after the function declaration in the c file. The VS compiler seems to be strict at this.

modifying the code:
void eStrRight (
VARCHAR_LATIN *str
,INTEGER *len
,VARCHAR_LATIN *result
,INTEGER *str_isNULL
,INTEGER *len_isNULL
,INTEGER *result_isNULL
,char sqlstate[6]
,SQL_TEXT fncname[129]
,SQL_TEXT sfncname[129]
,SQL_TEXT error_message[257]
)
{

int inlen;
int start;



It now works without any issues.

Just a heads up for those who want to use/play with these functions in a teradata windows machine.

kw
Enthusiast
You will also need to include the following definition if you use any of the UDFs that call strtoll (and you are using a Microsoft compiler) :

#define strtoll(x, y, z) _strtoi64(x, y, z)
Fan

Hi madmac, 

is there any Encoding function which is reverse of eURLDecodeEscapes function you provided in teradata.