?
: (Conditional)
---------------------------
The conditional operator (?:) returns one of two values depending on the value of a
Boolean expression
he following example returns the string "Overpaid" if Salary exceeds 10000.
@employees =
SELECT * FROM
( VALUES
(2, "Sophia", 100, (int?)15000, new DateTime(2012,03,19)),
(11, "Ethan", 400, (int?)9000, new DateTime(2015,08,22)),
(12, "David", 800, (int?)100, new DateTime(2016,11,01)),
(13, "Andrew", 100, (int?)null, new DateTime(1995,07,16))
) AS T(EmpID, EmpName, DeptID, Salary, StartDate);
@result =
SELECT EmpName,
Salary > 10000? "h" : Salary.ToString() AS Salary
FROM @employees;
OUTPUT @result
TO
"/Output/ReferenceGuide/BuiltInFunctions/CSharpFunctions/conditionalOperatorA.csv"
USING Outputters.Csv();
?? (Null-Coalescing)
---------------------------------
The ?? operator is called the null-coalescing operator. It returns the left-hand
operand if the operand is not null;
The following example returns 0 where Salary is null.
@employees =
SELECT * FROM
( VALUES
(2, "Sophia", 100, (int?)15000, new DateTime(2012,03,19)),
(11, "Ethan", 400, (int?)9000, new DateTime(2015,08,22)),
(12, "David", 800, (int?)100, new DateTime(2016,11,01)),
(13, "Andrew", 100, (int?)null, new DateTime(1995,07,16))
) AS T(EmpID, EmpName, DeptID, Salary, StartDate);
@result =
SELECT EmpName,
Salary ?? 0 AS Salary
FROM @employees;
OUTPUT @result
TO
"/Output/ReferenceGuide/BuiltInFunctions/CSharpFunctions/NullCoalescingOperator.csv
"
USING Outputters.Csv(outputHeader: true);
=> (Lamda)
----------------------------------------
he => token is called the lambda operator. It is used in lambda expressions to
separate the input variables on the left side from the lambda body on the right
side.
he following example shows two ways to find and display the length of the shortest
string in an array of strings.
@someBooks =
SELECT * FROM
( VALUES
("123; 1234; 12345; 123456"),
("1234; 12345; 123456")
) AS T(Books);
@array =
SELECT new SQL.ARRAY<string>(Books.Split(';')) AS BooksArray
FROM @someBooks;
@result =
SELECT BooksArray.Min(w => w.Length) AS MinLength,
BooksArray.Min((string w) => w.Length) AS MinLengthV2
FROM @array;
OUTPUT @result
TO "/Output/ReferenceGuide/BuiltInFunctions/CSharpFunctions/Lamda.csv"
USING Outputters.Csv(outputHeader: true);
String Methods
----------------------------------------------------------------------------------
Compare
---------------------
Compares two specified String objects and returns an integer that indicates their
relative position in the sort order.
@someData =
SELECT * FROM
( VALUES
("abc")
) AS T(string1);
@result =
SELECT String.Compare(string1, string1) AS samePosition,
String.Compare("ani\u00ADmal", "animal") AS ignorableCharacters,
String.Compare("Noah", "N") AS Noah_follows_N,
String.Compare("Noah", 0, "N", 0, 1) AS samePositionAtCount1,
String.Compare("abc", 0, "ABC", 0, 3) AS abc_precedes_ABC,
String.Compare("ABC", 0, "abc", 0, 3) AS ABC_follows_abc
FROM @someData;
OUTPUT @result
TO
"/Output/ReferenceGuide/BuiltInFunctions/CSharpFunctions/StringMethods/Compare.csv"
USING Outputters.Csv(outputHeader: true);
CompareTo
------------------------
This query uses CompareTo inthe WHERE clause to identify all employees with names
that start with "J" through "N."
@employees =
SELECT * FROM
( VALUES
--(1, "Noah", 100, (int?)10000, new DateTime(2012,05,31), "cell:030-
0074321,office:030-0076545"),
(2, "Sophia", 100, (int?)15000, new DateTime(2012,03,19), "cell:(5) 555-
4729,office:(5) 555-3745"),
(3, "Liam", 100, (int?)30000, new DateTime(2014,09,14), "cell:(5) 555-
3932"),
(4, "Amy", 100, (int?)35000, new DateTime(1999,02,27), "cell:(171) 555-
7788,office:(171) 555-6750, home:(425) 555-6238"),
(5, "Justin", 100, (int?)15000, new DateTime(2015,01,12), "cell:0921-12 34
65,office:0921-12 34 67"),
(6, "Emma", 200, (int?)8000, new DateTime(2014,03,08), (string)null),
(7, "Jacob", 200, (int?)8000, new DateTime(2014,09,02), ""),
(8, "Olivia", 200, (int?)8000, new DateTime(2013,12,11),
"cell:88.60.15.31,office:88.60.15.32"),
(9, "Mason", 300, (int?)50000, new DateTime(2016,01,01), "cell:(91) 555 22
82,office:(91) 555 91 99, home:(425) 555-2819"),
(10, "Ava", 400, (int?)15000, new DateTime(2014,09,14),
"cell:91.24.45.40,office:91.24.45.41"),
(11, "Ethan", 400, (int?)9000, new DateTime(2015,08,22), "cell:(604) 555-
4729,office:(604) 555-3745"),
(12, "David", 800, (int?)100, new DateTime(2016,11,01), "cell:(171) 555-
1212"),
(13, "Andrew", 100, (int?)null, new DateTime(1995,07,16), "cell:(1) 135-
5555,office:(1) 135-4892"),
(14, "Jennie", 100, (int?)34000, new DateTime(2000,02,12), "cell:(5) 555-
3392,office:(5) 555-7293")
) AS T(EmpID, EmpName, DeptID, Salary, StartDate, PhoneNumbers);
@result =
SELECT *
FROM @employees
WHERE EmpName.CompareTo("J") >= 0 AND EmpName.Substring(0, 1).CompareTo("N") <=
0;
OUTPUT @result
TO "/Output/ReferenceGuide/BuiltInFunctions/CSharpFunctions/CompareToB.csv"
ORDER BY EmpName ASC
USING Outputters.Csv();
Concat
-----------------------------------------
Concatenates one or more instances of String, or the String representations of the
values of one or more instances of Object.
@someData =
SELECT * FROM
( VALUES
("abc", "def", "The Book Thief; Markus Zusak; 2005")
) AS T(string1, string2, Books);
@result =
SELECT string1 AS originalString,
String.Concat(string1, string2) AS Concatenated,
"abc" + "def" AS concatenatedAltMethod,
//Create and array from Books, then concatenate the elements
String.Concat(new SQL.ARRAY<string>(Books.Split(';'))) AS
concatenatedArray
FROM @someData;
OUTPUT @result
TO
"/Output/ReferenceGuide/BuiltInFunctions/CSharpFunctions/StringMethods/Concat.csv"
USING Outputters.Csv(outputHeader: true);
Contains
------------------------------------------
Returns a value indicating whether a specified substring occurs within this string.
This query returns Films where Amy Adams is included as one of the MainActors.
@someFilms =
SELECT * FROM
( VALUES
("Trouble with the Curve", "Clint Eastwood, Amy Adams, Justin Timberlake,
Matthew Lillard, John Goodman"),
("American Hustle", "Christian Bale, Bradley Cooper, Amy Adams, Jeremy
Renner, Jennifer Lawrence"),
("Silver Linings Playbook", "Bradley Cooper, Jennifer Lawrence, Robert De
Niro, Jacki Weaver, Anupam Kher, Chris Tucker"),
("La La Land", "Ryan Gosling, Emma Stone, John Legend, Rosemarie DeWitt,
J.K. Simmons")
) AS T(Film, MainActors);
@result =
SELECT Film,
MainActors
FROM @someFilms
WHERE MainActors.Contains("Amy Adams");
OUTPUT @result
TO "/Output/ReferenceGuide/BuiltInFunctions/CSharpFunctions/Contains.csv"
USING Outputters.Csv(outputHeader: true);
EndsWith
-------------------------
Determines whether the end of this string instance matches the specified string.
This example returns records where PhoneNumber ends with 2738.
@someData =
SELECT * FROM
( VALUES
(2, "Sophia", "2535551234"),
(11, "Ethan", "4255552738"),
(12, "David", "4255550937"),
(13, "Andrew", "2068675309")
) AS T(EmpID, EmpName, PhoneNumber);
@result =
SELECT EmpName,
PhoneNumber
FROM @someData
WHERE PhoneNumber.EndsWith("2738");
OUTPUT @result
TO "/Output/ReferenceGuide/BuiltInFunctions/CSharpFunctions/EndsWith.csv"
USING Outputters.Csv();
Equals
--------------------------------------------
It compares the word Sophia with an equivalent word, its lowercase equivalent, and
its uppercase equivalent.
@someData =
SELECT * FROM
( VALUES
(2, "Sophia", "2535551234")
) AS T(EmpID, EmpName, PhoneNumber);
@result =
SELECT EmpName,
EmpName.Equals("Sophia") AS [Sophia],
EmpName.Equals("SOPHIA") AS [SOPHIA],
EmpName.Equals("sophia") AS [sophia]
FROM @someData;
OUTPUT @result
TO "/Output/ReferenceGuide/BuiltInFunctions/CSharpFunctions/Equals.csv"
USING Outputters.Csv(outputHeader: true);
Date time formats
----------------------------------------
DECLARE @now DateTime = DateTime.Now;
@stringFormats =
SELECT * FROM
( VALUES
// Standard
("ShortDatePattern",
Thread.CurrentThread.CurrentCulture.DateTimeFormat.ShortDatePattern),
("ToShortDateString", @now.ToShortDateString()),
("d", @now.ToString("d")),
((string)null, (string)null),
("LongDatePattern",
Thread.CurrentThread.CurrentCulture.DateTimeFormat.LongDatePattern),
("ToLongDateString", @now.ToLongDateString()),
("D", @now.ToString("D")),
((string)null, (string)null),
("LongDateShortTimePattern",
Thread.CurrentThread.CurrentCulture.DateTimeFormat.LongDatePattern + " " +
Thread.CurrentThread.CurrentCulture.DateTimeFormat.ShortTimePattern),
("f", @now.ToString("f")),
((string)null, (string)null),
("FullDateTimePattern",
Thread.CurrentThread.CurrentCulture.DateTimeFormat.FullDateTimePattern),
("F", @now.ToString("F")),
((string)null, (string)null),
("ShortDateShortTimePattern",
Thread.CurrentThread.CurrentCulture.DateTimeFormat.ShortDatePattern + " " +
Thread.CurrentThread.CurrentCulture.DateTimeFormat.ShortTimePattern),
("g", @now.ToString("g")),
((string)null, (string)null),
("ShortDateLongTimePattern",
Thread.CurrentThread.CurrentCulture.DateTimeFormat.ShortDatePattern + " " +
Thread.CurrentThread.CurrentCulture.DateTimeFormat.LongTimePattern),
("G", @now.ToString("G")),
((string)null, (string)null),
("MonthDayPattern",
Thread.CurrentThread.CurrentCulture.DateTimeFormat.MonthDayPattern),
("M", @now.ToString("M")),
((string)null, (string)null),
("RFC1123Pattern",
Thread.CurrentThread.CurrentCulture.DateTimeFormat.RFC1123Pattern),
("R", @now.ToString("R")),
((string)null, (string)null),
("SortableDateTimePattern",
Thread.CurrentThread.CurrentCulture.DateTimeFormat.SortableDateTimePattern),
("s", @now.ToString("s")),
((string)null, (string)null),
("ShortTimePattern",
Thread.CurrentThread.CurrentCulture.DateTimeFormat.ShortTimePattern),
("ToShortTimeString ", @now.ToShortTimeString()),
("t", @now.ToString("t")),
((string)null, (string)null),
("LongTimePattern",
Thread.CurrentThread.CurrentCulture.DateTimeFormat.LongTimePattern),
("ToLongTimeString ", @now.ToLongTimeString()),
("T", @now.ToString("T")),
((string)null, (string)null),
("UniversalSortableDateTimePattern",
Thread.CurrentThread.CurrentCulture.DateTimeFormat.UniversalSortableDateTimePattern
),
("u", @now.ToString("u")),
((string)null, (string)null),
("U", @now.ToString("U")),
((string)null, (string)null),
("YearMonthPattern",
Thread.CurrentThread.CurrentCulture.DateTimeFormat.YearMonthPattern),
("y", @now.ToString("y")),
((string)null, (string)null),
// Custom
("yyyy-MM-dd", @now.ToString("yyyy-MM-dd")),
("MMMM dd, yyyy", @now.ToString("MMMM dd, yyyy")),
("MM/dd/yy H:mm:ss", @now.ToString("MM/dd/yy H:mm:ss"))
) AS T(Property1, Value_DateTime);
OUTPUT @stringFormats
TO
"/Output/ReferenceGuide/BuiltInFunctions/CSharpFunctions/DateTime/stringFormats.txt
"
USING Outputters.Tsv();
String to DateTime
-------------------------------------
@convertString =
SELECT * FROM
( VALUES
("DateTime.Parse", "2/16/2008", DateTime.Parse("2/16/2008")),
("DateTime.Parse", "2/16/2008 12:15:12 PM", DateTime.Parse("2/16/2008
12:15:12 PM")),
("DateTime.Parse", "16/02/2008 12:15:12", DateTime.Parse("16/02/2008
12:15:12", new CultureInfo("fr-FR", false))),
("DateTime.Parse", "2/16/2008", DateTime.Parse("2/16/2008", new
CultureInfo("en-US"), DateTimeStyles.AssumeLocal)),
("Convert.ToDateTime", "2/16/2008", Convert.ToDateTime("2/16/2008")),
("Convert.ToDateTime", "2/16/2008 12:15:12 PM",
Convert.ToDateTime("2/16/2008 12:15:12 PM")),
("Convert.ToDateTime", "16/02/2008 12:15:12",
Convert.ToDateTime("16/02/2008 12:15:12", new CultureInfo("fr-FR", false))),
("Convert.ToDateTime", "2/16/2008", Convert.ToDateTime("2/16/2008", new
CultureInfo("en-US")))
) AS T(Method, String, Result);
OUTPUT @convertString
TO
"/Output/ReferenceGuide/BuiltInFunctions/CSharpFunctions/DateTime/convertString.txt
"
USING Outputters.Tsv();
Comparing DateTimes
-----------------------------------
@someDates =
SELECT * FROM
( VALUES
(1, new DateTime(2017, 01, 01, 00, 00, 00, DateTimeKind.Local), new
DateTime(2017, 01, 02, 01, 01, 01, DateTimeKind.Local)),
(2, new DateTime(2017, 01, 01, 00, 00, 00, DateTimeKind.Local), new
DateTime(2017, 01, 01, 00, 00, 00, DateTimeKind.Local)),
(3, new DateTime(2017, 01, 01, 01, 00, 00, DateTimeKind.Local), new
DateTime(2017, 01, 01, 00, 00, 00, DateTimeKind.Local))
) AS T(Id, Date1, Date2);
@compare =
SELECT // using Compare
DateTime.Compare(Date1, Date2) AS compareValue,
DateTime.Compare(Date1, Date2) < 0? Date1.ToString("G") + " is earlier
than " + Date2.ToString("G") :
(DateTime.Compare(Date1, Date2) == 0) ? Date1.ToString("G") + " is
the same as " + Date2.ToString("G") :
Date1.ToString("G") + " is later than " + Date2.ToString("G") AS
usingCompare,
// using CompareTo
Date1.CompareTo(Date2) AS compareToValue,
Date1.CompareTo(Date2) < 0 ? Date1.ToString("G") + " is earlier than "
+ Date2.ToString("G") :
(Date1.CompareTo(Date2) == 0) ? Date1.ToString("G") + " is the same
as " + Date2.ToString("G") :
Date1.ToString("G") + " is later than " + Date2.ToString("G") AS
usingcompareTo,
// using an operator
Date1 < Date2 ? Date1.ToString("G") + " is earlier than " +
Date2.ToString("G") :
Date1 == Date2 ? Date1.ToString("G") + " is the same as " +
Date2.ToString("G") :
Date1.ToString("G") + " is later than " + Date2.ToString("G") AS
usingOperator
FROM @someDates;
OUTPUT @compare
TO "/Output/ReferenceGuide/BuiltInFunctions/CSharpFunctions/DateTime/compare.csv"
USING Outputters.Csv(outputHeader: true);
