All Forums Database
zorbax19 1 post Joined 09/13
25 Oct 2013
How to make substring with the second or third occurrence of a charater

Hi,
I would like to take part of the string below by specifying a delimiter and an  occurrence.
EQN = AAL53CCW_400-5-13-3
If I want to recover what is on the left of the first occurrence of  '-' I can write SUBSTRING (EQN FROM 1 FOR POSITION ('-' IN EQN) - 1) which gives me  'AAL53CCW_400'
If I want to recover the portion of the string to the left of the second occurrence of '-'  'AAL53CCW_400-5’ I can write this in MySQL : SUBSTRING_INDEX (` EQN `,' - ', 2) where  2 represents occurrence of '-'.
I have not found a solution to realize this on teradata.
PS :  EQN chain length is variable and olso  the occurrence of character '-'
 
Many Thanks
 
Regards,
Frederic

M.Saeed Khurram 544 posts Joined 09/12
25 Oct 2013

You can use INSTR function to find the nth occurence of a character.
The syntax is as follows:

INSTR(string, substring [,position [, occurrence]])	

You can either specify position or occurence of character.
 

Khurram

dnoeth 4628 posts Joined 11/04
26 Oct 2013

To split a string there's also STRTOK in TD14:

STRTOK(EQN,'-',3)

This is also available as UDF pre-TD14 using the Ebay function:
http://developer.teradata.com/blog/madmac/2010/03/a-few-basic-scalar-string-udfs
 
Dieter

Dieter

anusuit1190 13 posts Joined 05/15
16 Mar 2016

I have a text in column that looks like this- 
/a/bbb/cc/dddd
 
I want to display bbb as my output. That is the text between 2nd "/" and 3rd "/" . How should I go about doing this?
 

sakthikrr 53 posts Joined 07/12
20 Mar 2016

Use STRTOK as suggested by Dieter:

STRTOK('/a/bbb/cc/dddd','/',2)

 

Sakthi

lr.aravena 1 post Joined 07/13
24 Mar 2016

Apparently these statements do not work in Teradata 15.00 :(

dnoeth 4628 posts Joined 11/04
24 Mar 2016

What do you mean by "do not work"?
STRTOK is built-in since TD14...

Dieter

yamyro 3 posts Joined 04/15
18 Aug 2016

Hi Dieter, Do you know if there is a bug in TERADATA  VERSION 14.00.03.02 for this fuction?, when I tried to use I had an error:
SELECT STRTOK (NULL,'|',2) --> SELECT Failed. 9134:  STRTOK: One of the parameters is NULL. 
In TERADATA  VERSION  14.00.07.15 works fine, and in the documentation I Found:
"If either instring or delimiter is NULL, the function returns NULL."
or maybe can be a configuration topic?
Thanks.

dnoeth 4628 posts Joined 11/04
19 Aug 2016

Maybe this issue was fixed in a version after 14.00.03.02, time to get the latest patch level (or upgrade)

Dieter

You must sign in to leave a comment.