Get index of element in U-SQL SqlArray?

Question

I'd like to be able to get the first index where a certain element appears in my SqlArray. Something like IndexOf(myArray, "word") would return 0 if the first element in myArray is "word".

Is there a way to do this? I've searched the documentation numerous times and what I understand is that SqlArray probably(?) implements IEnumerable under the hood, although there isn't any IndexOf function under IEnumerable either.

In case it matters, the way I've initialized my array is:

DECLARE CONST @namesStr = "a,b,c,d";
DECLARE CONST @names = new SqlArray<string>( @namesStr.Split(',') );

Answer 1

If you wanted to do this using Scala and Databricks, then you can just use indexOf, eg

val namesStr = "banana,apple,apricot"

// To array
val a = namesStr.split(",")

a.indexOf("apple")

My results:

If you do need to do this in U-SQL, then there is the IndexOf function you could use in a code-behind file, eg something like this:

using Microsoft.Analytics.Interfaces;
using Microsoft.Analytics.Types.Sql;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;

namespace USQLworking2
{
    public class myFunctions
    {
        public static int someFunction(SqlArray<string> someString, string searchString)
        {
            //!!TODO add error / exception handling

            String[] stringArray = someString.ToArray();

            return Array.IndexOf(stringArray, searchString);

        }
    }
}

And the U-SQL:

DECLARE @outputFile string = @"\output\output.csv";

DECLARE CONST @namesStr = "banana,apple,apricot";
DECLARE CONST @names = new SqlArray<string>( @namesStr.Split(',') );

@output =
    SELECT
        USQLworking2.myFunctions.someFunction ( @names, "apricot" ) AS shouldBe1
    FROM ( VALUES ( 0 ) ) AS x(y);


OUTPUT @output
TO @outputFile
USING Outputters.Csv();