Is INSTR type function available in Teradata...

UDA
Enthusiast

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...
5 REPLIES
Teradata Employee

Re: Is INSTR type function available in Teradata...

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 data

HTH.

Regards,

Adeel
Enthusiast

Re: Is INSTR type function available in Teradata...

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...
Teradata Employee

Re: Is INSTR type function available in Teradata...

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.aspx

Regards,

Adeel

hba
Teradata Employee

Re: Is INSTR type function available in Teradata...

Thanks Adeel.

The information you provied is very useful.

HBA
Enthusiast

Re: Is INSTR type function available in Teradata...

Adeel, I appreciate your prompt & useful information.

Thanks & Best Regards.