# Notes - Search text is case sensitive - Try to force attribute and text in your search to be in the same case - Wildcard search use STARTSWITH and ENDSWITH - two different types of nulls: - missing elements - actual null values. - Use IS_NULL()and IS_DEFINED() functions, to detect missing attributes and ternary logic - Cosmos DB SQL currently does not have an equivalent of SQL LEFT() function. Instead, use SUBSTRING() - There is no Year() function in Cosmos DB SQL, use SUBSTRING() to the date string and isolate - Use Truncate to Round numeric values ## Word Search SELECT s.Cars, s.Class FROM s WHERE CONTAINS(s.Cars, "ECO") ### Results: [ { "Cars": "LE ECO", "Class": "ELECTRIC" }, { "Cars": "ECO PREMIUM", "Class": "SEDAN" } ] ## Word Search (Substring) SELECT s.cars FROM s WHERE STARTSWITH(s.cars, "Golf") OR ENDSWITH(s.cars, "Diesel") ### Results: [ { "CustomerName": "Golf GTI" }, { "CustomerName": "Golf Passat" }, { "CustomerName": "All cars Diesel" } ] ## Case-insensitive Search SELECT s.CustomerName FROM s WHERE CONTAINS(UPPER(s.CustomerName), “eco") ## Null Handling in Cosmos DB SQL SELECT s.InvoiceNumber ,s.Cost + s.RepairsCost + s.PartsCost + s.TransportInCost + (IS_NULL(s.LineItemDiscount) ? 0 : s.LineItemDiscount) AS Costs FROM s ~~~~ Notes • (IS_NULL(s.LineItemDiscount) ? 0 : s.LineItemDiscount) -> If s.LineItemDiscount is NULL, then 0, otherwise s.LineItemDiscount • Problem, if attribute for s.LineItemDiscount is set to NULL, the Cost value will return nothing: ** GBPGB003, EURDE004, EURFR005 ~~~~ ### Results: [ { "InvoiceNumber": "GBPGB011", "Costs": 7425 }, { "InvoiceNumber": "GBPGB011", "Costs": 66400 }, { "InvoiceNumber": "GBPGB001" , "Costs": 56425 }, { "InvoiceNumber": "GBPGB002" , "Costs": 185650 }, { "InvoiceNumber": "GBPGB003" }, { "InvoiceNumber": "EURDE004" }, { "InvoiceNumber": "EURFR005" } ] ## Use IS_DEFINED() to detect if the property has been assigned a value SELECT s.InvoiceNumber ,s.Cost + s.RepairsCost + s.PartsCost + s.TransportInCost + (IS_DEFINED(s.LineItemDiscount) ? s.LineItemDiscount : 0) AS Costs FROM s ### Results: [ { "InvoiceNumber": "GBPGB011", "Costs": 7475 }, { "InvoiceNumber": "GBPGB011", "Costs": 66900 }, { "InvoiceNumber": "GBPGB001 , "Costs": 59125 }, { "InvoiceNumber": "GBPGB002" }, { "InvoiceNumber": "GBPGB003", "Costs": 16410 }, { "InvoiceNumber": "EURDE004", "Costs": 10600 }, { "InvoiceNumber": "EURFR005", "Costs": 17970 } ] ## SUBSTRING() for LEFT() SELECT s.CustomerName ,s.SalePrice FROM simplecars AS s WHERE SUBSTRING(s.CustomerName, 0, 1) = "M" ### Results: [ { "CustomerName": "Magic Motors", "SalePrice": 65000 } ] ## SUBSTRING() for RIGHT() SELECT s.CustomerName ,s.SalePrice FROM simplecars AS s WHERE SUBSTRING(REVERSE(s.CustomerName), 0, 1) = "r" ### Results: [ { "CustomerName": "WunderKar", "SalePrice": 11500 } ] ## For more than one character at the right of the string, use reverse string. Or, use a double reverse string to make it easier to read. SELECT s.CustomerName ,s.SalePrice FROM saleselements AS s WHERE REVERSE(SUBSTRING(REVERSE(s.CustomerName), 0, 3)) = "Kar" ## Impersonate T-SQL YEAR(), MONTH(), and DAY() SELECT s.SaleDate FROM s WHERE SUBSTRING(s.SaleDate, 0, 4) = "2015" The Month SELECT s.SaleDate FROM s WHERE SUBSTRING(s.SaleDate, 5, 2) = "02" ## The Date SELECT s.SaleDate FROM s WHERE SUBSTRING(s.SaleDate, 8, 2) = "25" ## Operators The operators >=, <=, <> or != IN for a set of years, etc. Rounding up to nearest 1000 TRUNC() function to round up to the nearest thousand: SELECT TRUNC(ROUND(s.TotalSalePrice / 1000)) * 1000 AS RoundedUp FROM s ### Results: [ { "RoundedUp": 89000 }, { "RoundedUp": 89000 }, { "RoundedUp": 65000 }, { "RoundedUp": 220000 }, { "RoundedUp": 20000 }, { "RoundedUp": 12000 }, { "RoundedUp": 20000 } ]