OREPLACE

Database

OREPLACE

I am interested in using the function OREPLACE in the TD_SYSFNLIB.  Can anyone tell me how to find out the max sizes of the input values.  It accepts source_string, search_string and replace_string.  I've looked in the documentation for String Operators and Functions but I don't see any information regarding the max size the values can be.  

Thanks

19 REPLIES
Enthusiast

Re: OREPLACE


JF,

According to the documentation the purpose of OREPLACE is to replace a part of string with another, to get the max string size you can use:

Select MAX(char_length(Col_Name)) from TableName
Khurram

Re: OREPLACE

What I'd like to know is what is the largest incoming value the OREPLACE function itself can process.  

Thanks

Enthusiast

Re: OREPLACE

oops, I thought you were asking about the string length.

Well, according to the documentation "The maximum length of a VARCHAR or CLOB result value is the maximum length that Teradata supports for these data types. An error is returned if the result string is larger than the maximum result string size."

The max length for VARCHAR and CHAR is 64000. 

Khurram
Senior Apprentice

Re: OREPLACE

It's 8000:

SELECT TYPE(OREPLACE('a', 'b', 'c'));

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

Type(OREPLACE('a','b','c'))
---------------------------------------
VARCHAR(8000) CHARACTER SET UNICODE
Enthusiast

Re: OREPLACE

Dieter,

I am surprized to know that, is there any enhancement in Teradata to define a column with 8000 varchar?

AFAIK a varchar can be defined upto 64000, and in case of unicode this limit is reduced to 32000. 

OREPLACE can be used with CLOB and UDT's as well, but I am confused with the Varchar limit. Please guide!

Khurram

Re: OREPLACE

Thanks Dieter.  Exactly what I needed.

Re: OREPLACE

Apparently there's a limit??!!

SELECT OREPLACE('I am interested in using the function OREPLACE in the TD_SYSFNLIB.  Can anyone tell me how to find out the max sizes of the input values.  It accepts source_string, search_string and replace_string.  Ive looked IN the documentation FOR String Operators AND Functions BUT I dont see any information regarding the max size the values can be.  Thanks. According to the documentation the purpose of OREPLACE is to replace a part of string with another, to get the max string size you can use: What Id like to know is what is the largest incoming value the OREPLACE function (TD_SYSFNLIB) itself can process.', 'TD_SYSFNLIB', '###########');

In the above query I'm trying to search and replace the text 'TD_SYSFNLIB' with the value '###########'. There are two instances and only the first gets replaced and the final string gets truncated.

Senior Apprentice

Re: OREPLACE

What's your TD release?

This works fime for me, double check if there's another UDF with the same name, either your defualt database or SYSLIB.

Teradata Employee

Re: OREPLACE

Got two 14.10 systems. One was upgraded from 13.10 and another one is a clean install of 14.10.

On the upgraded system SELECT TYPE(OREPLACE('a', 'b', 'c')); gives VARCHAR(16000), but on another its VARCHAR(8000). This means its changeable, right?

So, the question is how can i change this value on new system?

Mike