Hello I have a requirement wherein I have a table row containing a field having value such as ABCDE|12345A|187251D| and so on. The exact length is not known.
I want to write a query in SQL to split it at '|' delimiter and return the result as separate rows.
I cannot use any SQL function as it is not working in Salesforce Marketing Cloud. It has to be a query, i suppose.
Is there a way i can achieve that ? Pls advise.
I tried using PARSENAME but it is giving error
SELECT JobID as JobID, EmailId as EmailId, CustomerId as CustomerId, ConcatAlertId as ConcatAlertId, PARSENAME(REPLACE(ConcatAlertId, ’|’, ’.’), 1) as AlertId
Incorrect syntax near '’'.
and also i cannot check for how many delimiters it would need to run.
Will SPLIT_STRING work? how to use it here?
These below are restrictions for querying
The SQL support for the Query Activity is based on SQL Server 2005 capabilities.
Only SELECT statements to data extension or data views in an account or in the parent account
Most functions (i.e. MIN, MAX, etc) are supported
CAST and CONVERT
User Defined Functions
Transaction and Locking
Any sp_* stored procedure
Temporary Tables and Common Table Expressions