U-SQL splitting a column into two, delimited by "-"

Question

I am trying to split a column into two by using U-SQL in data lake analytics. In SQL could do it like this:

    ,CASE WHEN [Total] like '%-%' 
        THEN TRIM(LEFT([Total],CHARINDEX('-',[Total]) - 1)) END AS [TotalLeft]
    ,TRIM(REPLACE(SUBSTRING([Total],CHARINDEX('-',[Total]),LEN([Total])),'-','')) AS TotalRight

I tried something similar in U-SQL, but it seems that LEFT does not exist in U-SQL.

([Total] LIKE "%-%") ? Left([Total].IndexOf("-"), 1).Trim                   : 0 AS TotalLeft,

I read about using an array and EXPLODE, but this only seems to split it into more rows and not columns.

Furthermore I was thinking of using EXTRACT and set the delimiter to "-", but that does not seem an option either.

Anyone got any ideas on how to solve this efficiently? Thanks!

Answer 1

Here is an alternative approach using Split. It feels slightly more straightforward to me but there is always more than one way to do things. You don't need to use EXPLODE unless you want the elements to appear as rows. It's easier to extend if there are more than two items you want to split, Thanks to David for the sample query which I've reused.

@data =
    SELECT *
    FROM(
        VALUES
        ( "12-34" )
    ) AS T(col1);


@result =
    SELECT array[0] AS totalLeft,
           array[1] AS totalRight
    FROM
        (
            SELECT new SQL.ARRAY<string>(col1.Split('-')) AS array
            FROM @data
        ) AS x;


OUTPUT @result
TO "/output/result.txt"
USING Outputters.Tsv();