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 FROM Alerts_Today 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 Nested Queries UNION JOIN GROUP BY Conditional Statements IF Constructs CASE Statements Functions Most functions (i.e. MIN, MAX, etc) are supported CAST and CONVERT Unsupported elements Variables Cursors User Defined Functions Transaction and Locking GOTO PRINT Any sp_* stored procedure EXEC Temporary Tables and Common Table Expressions