Need Help in Teradata Functions

Tools

Need Help in Teradata Functions

I have a field in database which is in the below format

<string1>|<string2>|<string3> 

What SQL  function i should use to extract only <string2> from the above field.

Thanks,

Satya

5 REPLIES
Enthusiast

Re: Need Help in Teradata Functions

You can use  SUBSTRING or SUBSTR or REGEXP_SUBSTR.

Re: Need Help in Teradata Functions

I tried using SUBSTR but faced an error called string subscript out of bounds . Let me mention you the syntax i used


Select SUBSTR(SRC_KEY , 1 , INDEX(SRC_KEY , '|'  )-1)

FROM (

     SELECT  

     SUBSTR(Field,4,(LENGTH(ECTL_data_srce_key))) SRC_KEY , 

      FROM Table_Name

          ) A

Following are the sample records that i have 

P2|1004|P

P2|10005|1

P2|100006|1

P2|10000677|L

I need to read

1004

10005

100006

10000677

Can you plz suggest the syntax


Enthusiast

Re: Need Help in Teradata Functions

Just few days back someone had similar problem.

http://forums.teradata.com/forum/database/string-functions#comment-133182

example: select regexp_substr('P2|10000677|L','[^|]+',1,2) val ;

Junior Contributor

Re: Need Help in Teradata Functions

Hi Satya,

what's your TD release?

For this task there's a STRTOK function In TD14 (which is a bit easier to use than a  regular expression or INSTR):

STRTOK(col, '|', 2)

If you're on a previous release you might talk to your DBA to install one of the Ebay UDFs:

http://developer.teradata.com/blog/madmac/2010/03/a-few-basic-scalar-string-udfs

 eListGetValByIdx(col, '|', 2)
Enthusiast

Re: Need Help in Teradata Functions

Yes I tried with STRTOK(col, '|', 2), it is a bit easier. I m used to regexp and unix  these days :) . Thanks Dieter.