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

This should get you started. See also, C# Functions and Operators (U-SQL).

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

@result =
    SELECT 
    col1.Substring(0, col1.IndexOf("-")) AS totalLeft,
    col1.Substring(col1.IndexOf("-")+1) AS totalRight
FROM @data;

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