All Forums Database
JF230042 3 posts Joined 12/05
07 Jan 2014
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

M.Saeed Khurram 544 posts Joined 09/12
08 Jan 2014

 

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

JF230042 3 posts Joined 12/05
08 Jan 2014

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

M.Saeed Khurram 544 posts Joined 09/12
08 Jan 2014

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

dnoeth 4628 posts Joined 11/04
08 Jan 2014

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

 

Dieter

M.Saeed Khurram 544 posts Joined 09/12
08 Jan 2014

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

JF230042 3 posts Joined 12/05
08 Jan 2014

Thanks Dieter.  Exactly what I needed.

nomadic_being 3 posts Joined 09/14
30 Oct 2014

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.

dnoeth 4628 posts Joined 11/04
30 Oct 2014

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.

Dieter

MikeBell 3 posts Joined 05/14
20 Nov 2014

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

dnoeth 4628 posts Joined 11/04
20 Nov 2014

There might be an old oReplace C-UDF with a different definition in the syslib database.
If a function is not qualified the parser checks the current default db, syslib and td_sysfnlib in this order.

Dieter

MikeBell 3 posts Joined 05/14
20 Nov 2014

Thank you Dieter!
You were right, i already found this replacements in upgraded system. Do you know if there is another way to modify this value on new system?

Mike

shavyani 23 posts Joined 03/15
28 Oct 2015

Hello Folks ,
I just have a column data of the form :
'a','b','c','d'
I want to use Oreplace and replace it with  / symbol so that the resultant would be
a/b/c/d
I believe it should be nesting of Oreplace function but since single quotes is also a part of the delimiter to be replaced , its an issue.
Can any one help me achieve this.
Thanks in Advance.
Regards,
Shivyani

stevebarry 8 posts Joined 08/11
28 Oct 2015

Hi Shivyani,
You can escape single quotes by doubling them up, ie if you want a single quote literal within a string just use ''.
So in your example:

SEL '''a'',''b'',''c'',''d''' AS string,
    OREPLACE(OREPLACE(string,'''',''),',','/')

Regards,
Steve.

CarlosAL 512 posts Joined 04/08
28 Oct 2015

Hi.
You can do it in one blow with:
 
SELECT oTRANSLATE('''a'',''b'',''c'',''d''', 'a,''', 'a/');
 
HTH.
Cheers.
Carlos.

shavyani 23 posts Joined 03/15
28 Oct 2015

Hello Steve , Carlos ,
Thanks for your valuable suggestions.  I l try and get back!
 
Regards ,
Shavyani

shavyani 23 posts Joined 03/15
12 Nov 2015

Hello Carlos, Steve ,
I get your point , but my data is not a hardcoded value instead,
the issue now is there is a table t1 and it has Column (say Column1) data itself is of the form a','b','c','d so how can I apply this logic on the column1
Please Advise.
Thanks in Advance!
-Shavyani

 

dnoeth 4628 posts Joined 11/04
14 Nov 2015

Hi Shavyani,
Steve's query will work, this also:

TRIM(BOTH '''' FROM oREPLACE(string,''',''','/'))

 

Dieter

You must sign in to leave a comment.