ARRAYs

Overview

  • An ordered list of values (all of the same type)
  • Immutable
  • Single-Dimension

Sample data

Creating Empty arrays

new SqlArray<string> ( )

or

new SqlArray<string> { }

Creating arrays initialized with data

new SqlArray<string> { "A", "B" }

For example:

@output =
    SELECT * 
    FROM
    ( VALUES
        ( "West Virginia", 
            new SqlArray<string> { 
                "Charleston", "Huntington", "Parkersburg", "Morgantown", "Wheeling" 
        )
    )
AS T(State, Cities);

Creating an ARRAY from an IEnumerable

DECLARE @letters = new [] { "A", "B" };

@output = 
    SELECT         
        State,
        SqlArray.Create( @letters ) AS Cities
    FROM @cities;
State Cities
Vermont SqlArray{ "A", "B" }
Virginia SqlArray{ "A", "B" }
Washington SqlArray{ "A", "B" }
West Virginia SqlArray{ "A", "B" }
Wisconsin SqlArray{ "A", "B" }
Wyoming SqlArray{ "A", "B" }
@output = 
    SELECT         
        State,
        SqlArray.Create( Cities.Split(';') ) AS Cities
    FROM @cities;
State Cities
Vermont SqlArray{ "Burlington", "Essex", "South Burlington", "Colchester", "Rutland" }
Virginia SqlArray{ "Virginia Beach", "Norfolk", "Chesapeake", "Richmond", "Newport News" }
Washington SqlArray{ "Seattle", "Spokane", "Tacoma", "Vancouver", "Bellevue" }
West Virginia SqlArray{ "Charleston", "Huntington", "Parkersburg", "Morgantown", "Wheeling" }
Wisconsin SqlArray{ "Milwaukee", "Madison", "Green Bay", "Kenosha", "Racine" }
Wyoming SqlArray{ "Cheyenne", "Casper", "Laramie", "Gillette", "Rock Springs" }

Array Indexing

Use the array indexing operator [n] where n is a long. The first index is 0 – just like .NET

@output = 
    SELECT         
        State,
        SqlArray.Create( Cities.Split(';') ) AS Cities
    FROM @cities;


@output =
    SELECT
        State,
        Cities[0] AS FirstCity
    FROM @output;
State FirstCity
Vermont Burlington
Virginia Virginia Beach
Washington Seattle
West Virginia Charleston
Wisconsin Milwaukee
Wyoming Cheyenne

Removing members

@output = 
    SELECT         
        State,
        SqlArray.Create( Cities.Split(';') ) AS Cities
    FROM @cities;

@output =
    SELECT
        State ,
        SqlArray.Create( Cities.Where( c=>c.StartsWith("C") ) ) AS Cities
    FROM @output;
State Cities
Vermont SqlArray{ "Colchester" }
Virginia SqlArray{ "Chesapeake" }
Washington SqlArray{ }
West Virginia SqlArray{ "Charleston" }
Wisconsin SqlArray{ }
Wyoming SqlArray{ "Cheyenne", "Casper" }

Counting members

@output = 
    SELECT         
        State,
        SqlArray.Create( Cities.Split(';') ) AS Cities
    FROM @cities;

@output =
    SELECT
        State ,
        SqlArray.Create( Cities.Where( c=>c.StartsWith("C") ) ) AS Cities
    FROM @output;

@output =
    SELECT
        State ,
        Cities ,
        Cities.Count AS NumCities
    FROM @output;
State Cities NumCities
Vermont SqlArray{ "Colchester" } 1
Virginia SqlArray{ "Chesapeake" } 1
Washington SqlArray{ } 0
West Virginia SqlArray{ "Charleston" } 1
Wisconsin SqlArray{ } 0
Wyoming SqlArray{ "Cheyenne", "Casper" } 2

results matching ""

    No results matching ""