All Forums Tools
kimi.raj 2 posts Joined 05/14
22 May 2014
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

Raja_KT 1246 posts Joined 07/09
22 May 2014

You can use  SUBSTRING or SUBSTR or REGEXP_SUBSTR.
 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

kimi.raj 2 posts Joined 05/14
22 May 2014

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

 

 

 

Raja_KT 1246 posts Joined 07/09
22 May 2014

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 ;
 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

dnoeth 4628 posts Joined 11/04
22 May 2014

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)

 

Dieter

Raja_KT 1246 posts Joined 07/09
22 May 2014

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

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

You must sign in to leave a comment.