0

Im trying to find 20.000(starting 5000 rows in) row with a hotmail domain in email, but I not sure what sql are allowed in SFMC. Can anyone give some hints on a solution for this?

SELECT * 
FROM table LIMIT 5000,20000 
WHERE (substring_index(substr(email, instr(email, '@') + 1),'.',1)) = 'hotmail'

EDIT:

Im getting closer now, but it still dosnt work

SELECT * FROM ( 
  SELECT *, Row_number() OVER (ORDER BY cardid) AS row, Substring( [email address],(Charindex('@', [email address])+1),Len( [email address]) - (Charindex('@', [email address]))) AS domain FROM usableusers
 ) a WHERE a.row > 0 AND a.row <= 500 AND a.domain = 'hotmail.com'
1

Have you tried.

Select id, name from table where email like '%hotmail.com'

For more details check how to write queries on platform

https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select.htm

  • 1
    I think this is for a Marketing cloud Query activity, so it is T-SQL, not SOQL. – David Jackman Mar 1 '16 at 19:04
  • It looks like LIKE '%hotmail.com' works, now I just need to sort out the "range/row" issue – Bidstrup Mar 1 '16 at 19:27
  • Are you just trying to "select top 20000" from the table? Select top 20000 id, name from table where email like '%hotmail.com' – Jeremy Mar 2 '16 at 21:45
  • My first query would be the first 20000, the next query would then have to find the next 20000(so skip the first 20000) – Bidstrup Mar 6 '16 at 14:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.