Can STRtOK function return a null/0 value?

Question

I'm trying to use the strtok function to breakout a concatenated string. This is what I have so far. My source tables are sitting in Teradata and I'm running the code via SAS.

proc sql;
connect to teradata as tera ( server='XXXXXX' authdomain='XXXXX';
execute(
         update DB.Table1
         from 
       (
        select id, string_key
        from DB.Table2
        where date_time >= current_date
       ) c
set 
       country = STRTOK (c.string_key,',',1),
       Expense      = STRTOK (c.string_key,',',2),
       First_Name    = STRTOK (c.string_key,',',3),
       

) by tera
disconnect from tera
quit;

An example of a value in string_key is :

UK,244,Jack,Mathews

For the above example, my code has no problem creating the required output i.e:

Country	Expense	First name
UK	244	Jack

However in instances where a value in string_key has a null value after the delimiter, the strtok function returns the next available value in the wrong column.

for e.g when string_key is :

UK,244,,Mathews

then the output I get is

Country	Expense	First name
UK	244	Mathews

but what I want is that the First_name column should be empty as there is no value for it in the string_key.

i.e I want it is

Country	Expense	First name
UK	244

Could someone pls help tweak my code such that the column populates with a null value if the string has a null value?

Many thanks!

Answer 1

STRTOK doesn't play nicely with consecutive delimiters. You can replace consecutive delimiters with <delimiter><space><delimiter>. In your case:

trim(strtok(oreplace(string_key,',,',', ,'),',',3))

Alternatively, you could use csvld:

 select
 *
 from
 table (csvld(<table>.string_key,',','')
 returns(a varchar(100), b varchar(100), c varchar(100), d varchar (100))) as t;

Answer 2

A RegEx should work:

SELECT 'UK,244,,Mathews' AS string_key
  ,REGEXP_SUBSTR(string_key, '(,|^)\K([^,]*)(?=,|$)',1,1)
  ,REGEXP_SUBSTR(string_key, '(,|^)\K([^,]*)(?=,|$)',1,2)
  ,REGEXP_SUBSTR(string_key, '(,|^)\K([^,]*)(?=,|$)',1,3)
  ,REGEXP_SUBSTR(string_key, '(,|^)\K([^,]*)(?=,|$)',1,4)
;

See RegEx101