How to make substring with the second or third occurrence of a charater

Database

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

9 REPLIES

Re: How to make substring with the second or third occurrence of a charater

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
N/A

Re: How to make substring with the second or third occurrence of a charater

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

Re: How to make substring with the second or third occurrence of a charater

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?

Re: How to make substring with the second or third occurrence of a charater

Use STRTOK as suggested by Dieter:

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

Re: How to make substring with the second or third occurrence of a charater

Apparently these statements do not work in Teradata 15.00 :(

N/A

Re: How to make substring with the second or third occurrence of a charater

What do you mean by "do not work"?

STRTOK is built-in since TD14...

N/A

Re: How to make substring with the second or third occurrence of a charater

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.

N/A

Re: How to make substring with the second or third occurrence of a charater

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

Re: How to make substring with the second or third occurrence of a charater

SyntaxEditor Code Snippet

SELECT 
CASE WHEN REFERENCE_ID LIKE '%-%' THEN  SUBSTRING(REFERENCE_ID FROM 1 FOR POSITION('-' IN REFERENCE_ID) - 1)ELSE 'NO SUBSTRING REQUIRED' END
AS First_half,CASE 
WHEN REFERENCE_ID LIKE '%-%-%'
THEN SUBSTRING(REFERENCE_ID FROM POSITION('-' IN REFERENCE_ID) + 1 FOR POSITION('-' IN SUBSTRING(REFERENCE_ID FROM POSITION('-' IN REFERENCE_ID) + 1)) - 1)WHEN REFERENCE_ID LIKE '%-%'
THEN SUBSTRING(REFERENCE_ID FROM POSITION('-' IN REFERENCE_ID) + 1)ELSE  'NO SUBSTRING REQUIRED' 
END Second_Half