How to Split a string by delimited character and create new rows for every split using SQL query

Question

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

Answer 1

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

Answer 2

1. Export and Import back in with '|' as delimiter.
2. If columns in the field are set and permanent, use Substring() to split them.