Extracting value from comma-delimited string in Teradata

Question

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?

Did you try INSTR with occurrence parameter set to 5, and 6 and get the middle part? — tinazmu
– tinazmu, Commented Oct 2, 2024 at 0:22
I did not, I looked up an example and tried it, got this error: SUBSTR: string subscript out of bounds. (the example had me wrap INSTR with SUBSTR) — Larry Burholme
– Larry Burholme, Commented Oct 2, 2024 at 0:37
sure, instr will give you positions of commas, and substr will extract that part. You will need to check for several conditions; the first instr returns 0, second instr returns zero, first comma pos +1 = second comma pos (empty string); in these instances you will not call substr (I don't have access to a Teradata system to write the full expression) — tinazmu
– tinazmu, Commented Oct 2, 2024 at 1:38

tinazmu · Accepted Answer · 2024-10-02 03:22:41Z

2

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.

edited Oct 2, 2024 at 3:22

answered Oct 2, 2024 at 3:16

tinazmu

5,5442 gold badges12 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Larry Burholme Over a year ago

thanks, works great. I counted the commas in the field I am working with, filtered out any records with less than 6 commas, and then this works perfectly.

dnoeth · Accepted Answer · 2024-10-03 14:37:06Z

0

You can use a RegEx to extract the nth value:

-- this is an undocumented variant of regexp_substr which allows to 
-- extract a specific capture group using a 6th parameter 
regexp_substr_gpl  
  ( '1,,,,,012345678,abcdzyz,,,,,,'
   ,'([^,]*+),?+'     -- any numer of non-commas followed by an optional comma
   ,1   -- startpos
   ,6   -- this is the nth occurence
   ,'c' -- case insensitive
   ,1   -- extract first capture group <---
  )

answered Oct 3, 2024 at 14:37

dnoeth

60.8k4 gold badges45 silver badges63 bronze badges

Collectives™ on Stack Overflow

Extracting value from comma-delimited string in Teradata

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related