0

I have created logic to fetch data from string field:

Input :

dbname,tblname,comment,tablekind
sample_1,table1,Test_SALES - SALES & PROD - Orders - flow - CORP - test_1@gmail.com - 
test_2@gmail.com,T
sample_2,table2,Test_Finance  -  FS  - PK - fs.global@GMAIL.com - fs1@gmail.com

Output :

dbname,tblname,domain,product,email
sample_1,table_1,Test_SALES,SALES & PROD,test_1@gmail.com;test_2@gmail.com
sample_2,table_2,Test_Finance,FS,fs.global@GMAIL.com;fs1@gmail.com

I have tried creating this query but getting error STRTOK: InputString or Delim length is 0; OR toknum parameter is no larger than 0 if comment is blank or null:

select dbname, tblname,comment,STRTOK(comment,'-',1) as domain,STRTOK(comment,'- ',2) as 
product,
(cast(regexp_substr(comment, '(\b[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,6}\b)', 1, 
1, 'i') as varchar(128))        
|| coalesce(',' ||cast(regexp_substr(comment, '(\b[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A- 
Za-z]{2,6}\b)', 1, 2, 'i')as varchar(128)), '') 
|| coalesce(',' ||cast(regexp_substr(comment, '(\b[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A- 
Za-z]{2,6}\b)', 1, 3, 'i')as varchar(128)), '') 
|| coalesce(',' ||cast(regexp_substr(comment, '(\b[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A- 
Za-z]{2,6}\b)', 1, 4, 'i')as varchar(128)), '')  
|| coalesce(',' ||cast(regexp_substr(comment, '(\b[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A- 
Za-z]{2,6}\b)', 1, 5, 'i')as varchar(128)), '')) as email
from dbc.tables where tablekind in ('T','V')
Sak
  • 61
  • 6
  • 1
    To work around it you could do something like `CASE WHEN LENGTH(comment) > 0 THEN comment ELSE NULL END` – Fred Jun 09 '23 at 16:26
  • Thank you,if we have the data like below : sample_2,table2,Test-Finance - FS - PK - fs.global@GMAIL.com - fs1@gmail.com how to fetch the output as sample_2,table2,Test-Finance,fs,pk, fs.global@GMAIL.com;fs1@gmail.com – Sak Jun 28 '23 at 16:43
  • Instead of `STRTOK(comment,'-',1)` for example, you could use `STRTOK(CASE WHEN LENGTH(comment) > 0 THEN comment ELSE NULL END,'-',1)` – Fred Jun 28 '23 at 18:01
  • Thanks Fred,I recently observed in email field sometimes I am getting same email ids.Example :abc@test.com,ABC@test.com.In this case I just need to keep one email id.Any idea how can I achieve this within the logic. – Sak Jul 12 '23 at 11:25
  • Conceptually, in SQL you could "pivot" to one row per email ID, remove duplicates (e.g. DISTINCT) and then "unpivot" back to one row per table/view. In this specific case I would probably look at using REGEXP_SPLIT_TO_TABLE function rather than PIVOT operator and NPATH function rather than UNPIVOT operator. The syntax can be rather complicated so check the documentation and search SO for examples. – Fred Jul 12 '23 at 14:59
  • Hi Fred,Thank you for your help. I have tried it but its not working. Can you please help, how can I use regex_split_to_table in the above code of email. – Sak Jul 13 '23 at 08:18

0 Answers0