CODEWITHSUNDEEP
sqlteradata
ihadanny
ihadanny

Reputation: 4483

using teradata syslib.oreplace to remove all whitespace characters

I'm trying to remove all white space characters (tab, cr, lf) and the best expression I've found (that actually works) is:

syslib.oreplace(
 syslib.oreplace(
  syslib.oreplace(my_string,X'0a',''),X'0d',''),X'09','')

is there anything more elegant? can't I remove all of them in a single oreplace call?

Upvotes: 3

Views: 4373

Answers (2)

Dave
Dave

Reputation: 1

won't this work?

sel COALESCE( OREPLACE('no spaces',' '), '')

Upvotes: 0

Rob Paller
Rob Paller

Reputation: 7786

I have used the following UDF which is based on the isprint() C function:

#define SQL_TEXT Latin_Text
#include "sqltypes_td.h"
#include <ctype.h>
#include <string.h>
#include <stdlib.h>
/*
||-------------------------------------------------------------------
||  retain_print()                                   
||
|| Description:
||  Accepts an input string and returns only the printable characters
||  including space based on the isprint() C function.
||
||  Note: tabs, vertical tabs, form feeds, newlines, and carriage
||  returns will be stripped. Otherwise, the isspace() function
||  would have been used to check for space.
||
|| Arguments:                                     
||     sStrIn:     string to be processed                    
||     result:     processed string or spaces if invalid.   
||
|| Examples:
||    retain_print("ABCD. 1234")
||      returns "ABCD. 1234"
||
|| Sample Use:
||    SELECT nullif(retain_print('ABCD. 1234'), ' ');
||-------------------------------------------------------------------
*/
void retain_print( VARCHAR_LATIN  *sStrIn,
                   VARCHAR_LATIN  *result)
{
    int  slen;          /* sStrIn length in chars   */
    int  iposin;        /* position of input string */
    int  iposout;       /* position of output string */
    char strout[10000];   /* output string */         
    /*
    ||------------------------------------------------
    || Loop thru testing for alpha.                   
    ||------------------------------------------------
    */
    iposin = 0;
    iposout = 0;
    slen = strlen((const char *) sStrIn); 
    if (slen < 1) {
        goto ERROR;
      }
    while ((slen > iposin)) {
        /* Check if argument is a printable character (including a space) */
        if (isprint(sStrIn[iposin])) {
            strout[iposout] = sStrIn[iposin];
            iposout = iposout + 1;
        }
        /* The argument is not a printable character replace with a space */
        else {
            strout[iposout] = ' ';
            iposout = iposout + 1;        
        }
        iposin = iposin + 1;
    }
    strout[iposout] = '\0';
    strcpy((char *) result, strout);
    goto EXIT;
ERROR:
    strcpy((char *) result, " ");
EXIT:
    return;

Upvotes: 1

Related Questions