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!

wBob · Answer 1 · 2018-01-08T22:11:15.837

5

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();

edited Jan 08 '18 at 22:11

answered Jan 08 '18 at 22:02

wBob

13,710
3
20
37

Two thumbs up!! – David Paul Giroux Jan 09 '18 at 05:49
Great, I did not expect quick replies like this. Thanks a lot both of you! – Richard Jan 09 '18 at 08:16

score 4 · Accepted Answer · answered Jan 08 '18 at 17:24

4

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();

answered Jan 08 '18 at 17:24

David Paul Giroux

657
4
10

Great, I did not expect quick replies like this. Thanks a lot both of you! – Richard Jan 09 '18 at 08:15

score 4 · Answer 3 · answered Jan 15 '19 at 11:56

Just adding my two cents here. There is an even shorter version.

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

@result =
    SELECT 
    col1.Split('-')[0] AS totalLeft,
    col1.Split('-')[1] AS totalRight
FROM @data;

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

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

3 Answers3