3

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
  • 1
    PARSENAME should work you are using special quotes use ' rather that ’ – benahm Mar 20 '17 at 20:21
2

Could you try this

SELECT JobID as JobID, EmailId as EmailId, CustomerId as CustomerId, ConcatAlertId as ConcatAlertId, PARSENAME(REPLACE(ConcatAlertId, '|', '.'), 1) as AlertId
FROM Alerts_Today
| improve this answer | |
  • Hi, that worked. Thanks. Also can you tell me how to use the statement to check through all the pipe delimiters present in the string. – Pankaj Gupta Mar 20 '17 at 20:30
0
  1. Export and Import back in with '|' as delimiter.
  2. If columns in the field are set and permanent, use Substring() to split them.
| improve this answer | |
  • We are not exporting/ importing data. These values are already in a table and we are trying to use SELECT query to populate another Data Extension by splitting the text around delimiter. – Pankaj Gupta Mar 20 '17 at 19:44

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.