sqlteradata

Extracting value from comma-delimited string in Teradata

I need the value extracted from between the 5th and 6th comma of a string. STRTOK() is skipping the positions that are empty and not counting them. For example, this returns null because there are not 6 instances of values between commas:

SELECT STRTOK('1,,,,,012345678,abcdzyz,,,,,,', ',', 6) AS extracted_value;

I have tried the popular AI sites and none of the solutions are working. Can this be done in Teradata, or should I do it in python?

Solution

  • This should work on your sample string (strThis being your string):

    SUBSTR(strThis, INSTR(strThis, ',', 1, 5) + 1, INSTR(strThis, ',', 1, 6) - INSTR(strThis, ',', 1, 5) - 1)

    if your string has the 5th and the 6th commas in it; you need to check for all other conditions.