Skip to content

Instantly share code, notes, and snippets.

View bertwagner's full-sized avatar

Bert Wagner bertwagner

52 followers · 4 following
View GitHub Profile
@bertwagner
bertwagner / LaxVersusStrict.sql
Last active April 16, 2024 23:03
SQL Server 2016 JSON's Lax versus Strict modes
-- Lax (default: function will return an error if invalid JSON path specified
SELECT JSON_VALUE('{ "Color" : "Red" }', '$.Shape') --lax is the default, so you don't need to be explicitly state it
-- Output: NULL
SELECT JSON_VALUE('{ "Color" : "Red" }', 'lax $.Shape')
-- Output: NULL
-- Strict: function will return an error if invalid JSON path specified
SELECT JSON_VALUE('{ "Color" : "Red" }', 'strict $.Shape')
-- Output: Property cannot be found on the specified JSON path.
@bertwagner
bertwagner / JSON date time in SQL .sql
Last active January 26, 2023 20:30
JSON date times converting to SQL data types
DECLARE @jsonData nvarchar(max) = N'{ "createDate" : "2017-03-28T12:45:00.000Z" }'
-- SQL's JSON_VALUE() will read in the JSON date time as a string
SELECT JSON_VALUE(@jsonData, '$.createDate')
-- Output: 2017-03-28T12:45:00Z
-- If we want to read it in as a SQL datetime2, we need to use a CONVERT() (or a CAST())
SELECT CONVERT(datetime2, JSON_VALUE(@jsonData, '$.createDate'))
-- Output: 2017-03-28 12:45:00.0000000
@bertwagner
bertwagner / Create Car Inventory Data.sql
Created June 17, 2017 11:50
Dataset for demos of how to solve 5 business problems with temporal tables https://medium.com/@bertwagner/5-business-problems-you-can-solve-with-temporal-tables-363255ed3a8c
-- Drop and create our temporal and historical tables
IF OBJECT_ID('dbo.CarInventory', 'U') IS NOT NULL
BEGIN
-- When deleting a temporal table, we need to first turn versioning off
ALTER TABLE dbo.CarInventory SET ( SYSTEM_VERSIONING = OFF )
DROP TABLE dbo.CarInventory
DROP TABLE dbo.CarInventoryHistory
END;
CREATE TABLE CarInventory
(
@bertwagner
bertwagner / RSSFeeds.opml
Created July 15, 2019 19:33
The OPML file of all of the SQL Server (and some other) RSS feeds I subscribe to as of 2019-07-01.
<?xml version="1.0" encoding="UTF-8"?>
<opml version="1.0">
<head>
<title>Bert's mostly SQL subscriptions in feedly Cloud</title>
</head>
<body>
<outline text="Marketing" title="Marketing">
<outline type="rss" text="Signal v. Noise" title="Signal v. Noise" xmlUrl="https://signalvnoise.com/posts.rss" htmlUrl="https://m.signalvnoise.com"/>
<outline type="rss" text="Austin Kleon" title="Austin Kleon" xmlUrl="http://feeds2.feedburner.com/AustinKleon" htmlUrl="https://austinkleon.com"/>
@bertwagner
bertwagner / JSON_MODIFY 4.sql
Last active March 10, 2020 18:43
SQL Server 2016 JSON's MODIFY_JSON Delete Array Element
DECLARE @garage nvarchar(1000) = N'{ "Cars": [{ "Make": "Volkswagen", "Model": { "Base": "Golf", "Trim": "GLI" }, "Year": 2003, "SellDate" : "2017-02-17T00:00:00.000Z" }, { "Make": "Subaru", "Model": { "Base": "Impreza", "Trim": "Premium" }, "Year": 2016, "PurchaseDate": "2015-08-18T00:00:00.000Z" },{ "Make": "Volkswagen", "Model": { "Base": "Golf", "Trim": "GTI" }, "Year": 2017, "PurchaseDate": "2017-02-19T00:00:00.000Z" }] }'
-- I realize it's not worth keeping the original Volkswagen in my @garage data any longer, so let's completely remove it.
-- Note, if we use NULL as per the MSDN documentation, we don't actually remove the first car element of the array - it just gets replaced with NULL
-- This is problematic if we expect the indexes of our array to shift by -1.
SELECT JSON_MODIFY(@garage, '$.Cars[0]', NULL)
-- Output: { "Cars": [null, { "Make": "Subaru", "Model": { "Base": "Impreza", "Trim": "Premium" }, "Year": 2016, "PurchaseDate": "2015-08-18T00:00:00.000Z" },{ "Make": "Volkswagen", "Model": { "Ba
@bertwagner
bertwagner / Get-A-Quote-Exercise-1-Skill.json
Created November 15, 2019 14:36
{
"name": "Get a Quote",
"intents": [
{
"intent": "AutoInsurance",
"examples": [
{
"text": "auto"
},
{
@bertwagner
bertwagner / CascadingDropdown-Years.js
Last active February 18, 2019 12:41
[{
"Label": "2015",
"Value": "2015"
}, {
"Label": "2016",
"Value": "2016"
}, {
"Label": "2017",
"Value": "2017"
}]
@bertwagner
bertwagner / XmlVsJson.sql
Created May 12, 2017 00:15
This file has been truncated, but you can view the full file.
USE Sandbox;
DROP TABLE IF EXISTS dbo.XmlVsJSON
CREATE TABLE dbo.XmlVsJson
(
Id INT IDENTITY PRIMARY KEY,
XmlData XML,
JsonData NVARCHAR(MAX)
)
@bertwagner
bertwagner / YellowstoneParkBoundary.geojson
Created January 12, 2018 23:13
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
@bertwagner
bertwagner / Earthquakes.geojson
Created January 12, 2018 22:43
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.