All Forums UDA
shakir 6 posts Joined 01/08
18 Nov 2008
Is INSTR type function available in Teradata...

Hi,I want to get some part of data from a string column which has..."1234567890"" abcd#efgh#ijkl#mnop#qrst#uvwx#yz#0000"" abcd#efgh#ijkl#mnop#qrst#1234#yz#0123"" abcd#efgh#ijkl#mnop#qrst#aaaa#yz#0111"" abcd#efgh#ijkl#mnop#qrst"now, if the column value has 6 hashes then I want to get the data between 5th and 6th hash.How to know number of occurences of a particular string in a string value and know the position of nth occurence.Thanks in advance...

Adeel Chaudhry 773 posts Joined 04/08
18 Nov 2008

Hello,This can be done in three ways, which are as follows:- Write custom UDF which returns number of occurences within a string- Use UDF INSTR from http://www.teradata.com/DownloadCenter/Topic9228-137-1.aspx- Use query with multiple SUBSTR and INDEX functions for filtering dataHTH.Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

shakir 6 posts Joined 01/08
18 Nov 2008

Thanks Adeel.I have downloaded the functions to create the UDF but I could not compile them bcoz of below error."ODBC HY000: User Defined Function source on the client is not supported by the driver."I appreciate, if you can give a small example of how to use SUBSTRING & INDEX to achieve this...Thanks in advance...

Adeel Chaudhry 773 posts Joined 04/08
18 Nov 2008

UDF cannot be compiled using ODBC, try a BTEQ script in that zip file, offcourse, using bteq; and don't forget to change the database name.For example of SUBSTR and INDEX you can check following URL:http://www.teradata.com/teradataforum/FindPost13471.aspxRegards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

hba 3 posts Joined 06/08
18 Nov 2008

Thanks Adeel.The information you provied is very useful.HBA

shakir 6 posts Joined 01/08
22 Nov 2008

Adeel, I appreciate your prompt & useful information. Thanks & Best Regards.

You must sign in to leave a comment.