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

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

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.


dnoeth 4628 posts Joined 11/04
26 Oct 2013

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


This is also available as UDF pre-TD14 using the Ebay function:


anusuit1190 13 posts Joined 05/15
16 Mar 2016

I have a text in column that looks like this- 
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:




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


yamyro 3 posts Joined 04/15
18 Aug 2016

Hi Dieter, Do you know if there is a bug in TERADATA  VERSION 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 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?

dnoeth 4628 posts Joined 11/04
19 Aug 2016

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


You must sign in to leave a comment.