# 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
    }
    ]