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 '-'
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.
To split a string there's also STRTOK in TD14:
This is also available as UDF pre-TD14 using the Ebay function:
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?
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?
Maybe this issue was fixed in a version after 14.00.03.02, time to get the latest patch level (or upgrade)
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