0

I do not have access to write to a database, queries are limited to retrieval only. CTEs are not allowed. Cannot write functions. (Here's a document of what I'm limited to https://help.salesforce.com/articleView?id=mc_as_sql_reference.htm&type=5)

Here's an example of a string I'm working with, I'm looking to extract the email address, where I consistently know that the email will begin at the fourth |, and end at the fifth |.

D||John Smith|EML|test@gmail.com|Y|2014/01/03 17:14:01.000000|

This is what I tried so far, it's not able to return any email addresses for me

SELECT 
    CASE
        WHEN CHARINDEX('|',AllData) > 0 
           THEN SUBSTRING(AllData, CHARINDEX('|', AllData, 22) + 1, ABS((CHARINDEX('|', AllData, CHARINDEX('@', AllData))) - (CHARINDEX('|', AllData, 22) + 1)))
           ELSE 'NotWorking'
    END AS email
FROM
    [test_file]
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Julie Nguyen
  • 29
  • 4
  • You might do better looking in the Land of [Salesforce.SE](https://salesforce.stackexchange.com/), e.g. searching for [`SQL split`](https://salesforce.stackexchange.com/search?q=sql+split). – HABO Nov 09 '20 at 19:31
  • No success in Salesforce world, was hoping to look to this one to see if I could get advice – Julie Nguyen Nov 09 '20 at 19:51

1 Answers1

2

I would suggest string_split():

select s.value
from test_file tf cross apply
     string_split(alldata, '|') s
where s.value like '%@%.%';

This doesn't extract the fourth value. It extracts any value that looks like an email -- but it is pretty simple code that should work.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786