Practical example:

DECLARE @v1 AS VECTOR(2) = '[25.220200126129058, 55.27695446131732]'; -- Dubai
DECLARE @v2 AS VECTOR(2) = '[41.011715197431464, 28.95562641991561]'; -- Istanbul

SELECT VECTOR_DISTANCE('euclidean', @v1, @v2) AS euclidean,
       VECTOR_DISTANCE('cosine', @v1, @v2) AS cosine,
       VECTOR_DISTANCE('dot', @v1, @v2) AS dot;
GO

DECLARE @v3 AS VECTOR(4);
DECLARE @v4 AS VECTOR(4);

SELECT @v3 = v.Embedding FROM dbo.Vectors AS v WHERE v.DocId = 1
SELECT @v4 = v.Embedding FROM dbo.Vectors AS v WHERE v.DocId = 2

SELECT VECTOR_DISTANCE('euclidean', @v3, @v4) AS euclidean,
       VECTOR_DISTANCE('cosine', @v3, @v4) AS cosine,
       VECTOR_DISTANCE('dot', @v3, @v4) AS dot;
GO

SELECT  v.Embedding, VECTOR_NORMALIZE(Embedding, 'norm1') AS norm1
        , VECTOR_NORMALIZE(Embedding, 'norm2') AS norm2
        , VECTOR_NORMALIZE(Embedding, 'norminf') AS norminf
    FROM dbo.Vectors AS v
GO

Practical example:

-- Enable the external REST endpoint invocation on the database server
EXECUTE sp_configure 'external rest endpoint enabled', 1;
RECONFIGURE WITH OVERRIDE;
GO

-- Create a database master key
IF NOT EXISTS (SELECT *
               FROM sys.symmetric_keys
               WHERE [name] = '##MS_DatabaseMasterKey##')
    BEGIN
        CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'';
    END
GO

-- Create access credentials to Azure OpenAI using a key
CREATE DATABASE SCOPED CREDENTIAL [https://my-azure-openai-endpoint.cognitiveservices.azure.com/]
    WITH IDENTITY = 'HTTPEndpointHeaders', secret = '{"api-key":"YOUR_AZURE_OPENAI_KEY"}';
GO

-- Create an external model to call the Azure OpenAI embeddings REST endpoint
CREATE EXTERNAL MODEL MyAzureOpenAIModel
WITH (
      LOCATION = 'https://my-azure-openai-endpoint.cognitiveservices.azure.com/openai/deployments/text-embedding-ada-002/embeddings?api-version=2023-05-15',
      API_FORMAT = 'Azure OpenAI',
      MODEL_TYPE = EMBEDDINGS,
      MODEL = 'text-embedding-ada-002',
      CREDENTIAL = [https://my-azure-openai-endpoint.cognitiveservices.azure.com/]
);
GO

-- Create a table with text to chunk and insert data
CREATE TABLE textchunk
(
    text_id INT IDENTITY (1, 1) PRIMARY KEY,
    text_to_chunk NVARCHAR (MAX)
);
GO

INSERT INTO textchunk (text_to_chunk)
VALUES ('Your chenk text 1'),
       ('Your chenk text 2');
GO

-- Create a new table to hold the chunked text and vector embeddings
CREATE TABLE text_embeddings
(
    embeddings_id INT IDENTITY (1, 1) PRIMARY KEY,
    chunked_text NVARCHAR (MAX),
    vector_embeddings VECTOR(1536)
);
GO

-- Insert the chunked text and vector embeddings into the text_embeddings table 
-- using AI_GENERATE_CHUNKS and AI_GENERATE_EMBEDDINGS:
INSERT INTO text_embeddings (chunked_text, vector_embeddings)
    SELECT c.chunk,
           AI_GENERATE_EMBEDDINGS(c.chunk USE MODEL MyAzureOpenAIModel)
        FROM textchunk AS t
            CROSS APPLY AI_GENERATE_CHUNKS (
                SOURCE = t.text_to_chunk,
                CHUNK_TYPE = FIXED,
                CHUNK_SIZE = 100
            ) AS c;
GO

-- View the results
SELECT *
    FROM text_embeddings;

All SARGable.

This implementation of regular expression is based on the RE2 regular expression library. For more information, visit RE2 Regular Expression Syntax

Practical example:

USE AdventureWorks2022
GO
ALTER DATABASE AdventureWorks2022 SET COMPATIBILITY_LEVEL = 170;
GO

-- Select all records from the Person table where the first name starts with R and ends with b
SELECT *
    FROM [Person].[Person] AS p
    WHERE REGEXP_LIKE (p.FIRSTNAME, '^R.*b$');

-- Select all records from the Person table where the first name starts with A and ends with Y, using case-insensitive mode
SELECT *
    FROM [Person].[Person] AS p
    WHERE REGEXP_LIKE (p.FIRSTNAME, '^R.*B$', 'i');

-- Select all records from the Products table where the product name contains at least one consecutive vowels:
SELECT *
    FROM [Production].[Product] AS p
    WHERE REGEXP_LIKE (p.NAME, '[AEIOU]{1,}');

-- Create an employees table with CHECK constraints for the Email and Phone_Number columns:
DROP TABLE IF EXISTS Employees;
CREATE TABLE Employees
(
    ID INT IDENTITY (101, 1),
    [Name] VARCHAR (150),
    Email VARCHAR (320)
        CHECK (REGEXP_LIKE (Email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')),
    Phone_Number NVARCHAR (20)
        CHECK (REGEXP_LIKE (Phone_Number, '^(\d{3})-(\d{3})-(\d{4})$'))
);

-- Replace all occurrences of a or e with - in the product names.
SELECT p.NAME, REGEXP_REPLACE(p.NAME, '[ae]', '-', 1, 0, 'i')
    FROM [Production].[Product] AS p;

-- Replace the first occurrence of cat or dog with pet in the product descriptions
SELECT pd.DESCRIPTION, REGEXP_REPLACE(pd.DESCRIPTION, 'cat|dog', 'pet', 1, 1, 'i')
    FROM [Production].[ProductDescription] AS pd;

-- Replace the last four digits of the phone numbers with asterisks
SELECT pp.PHONENUMBER, REGEXP_REPLACE(pp.PHONENUMBER, '\d{4}$', '****')
    FROM [Person].[PersonPhone] AS pp;

-- Extract the domain name from an email address.
SELECT ea.EmailAddress,  REGEXP_SUBSTR(ea.EmailAddress, '@(.+)$', 1, 1, 'i', 1) AS DOMAIN
    FROM [Person].[EmailAddress] AS ea;

-- Find the first word in a sentence that starts with a vowel.
SELECT pd.DESCRIPTION, REGEXP_SUBSTR(pd.DESCRIPTION, '\b[aeiou]\w*', 1, 1, 'i') AS WORD
    FROM [Production].[ProductDescription] AS pd;

-- Get the last four digits of a credit card number.
SELECT cc.CARDNUMBER, REGEXP_SUBSTR(cc.CARDNUMBER, '\d{4}$') AS LAST_FOUR
    FROM [Sales].[CreditCard] AS cc;

-- Find the position of the first substring that contains only digits in the DESCRIPTION column.
SELECT pd.DESCRIPTION, REGEXP_INSTR(pd.DESCRIPTION, '\d+')
    FROM [Production].[ProductDescription] AS pd;

-- Find the position of the third occurrence of the letter a (case-insensitive) in the NAME column.
SELECT p.NAME, REGEXP_INSTR(p.NAME, 'a', 1, 3, 0, 'i')
    FROM [Production].[Product] AS p;

-- Find the position of the end of the first substring that starts with t and ends with e (case-sensitive) in the DESCRIPTION column.
SELECT pd.DESCRIPTION, REGEXP_INSTR(pd.DESCRIPTION, 't.*?e', 1, 1, 1)
    FROM [Production].[ProductDescription] AS pd;

-- Count how many times the letter a appears in each product name.
SELECT p.NAME, REGEXP_COUNT(p.NAME, 'a') AS A_COUNT
    FROM [Production].[Product] AS p;

-- Count how many products have a name that ends with ing.
SELECT COUNT(*)
    FROM [Production].[Product] AS p
    WHERE REGEXP_COUNT(p.NAME, 'ing$') > 0;

-- Count how many products have a name that contains three consecutive consonants, ignoring case.
SELECT COUNT(*)
    FROM [Production].[Product] AS p
    WHERE REGEXP_COUNT(p.NAME, '[^aeiou]{3}', 1, 'i') > 0;

-- Return tabular results from 'Learning #AzureSQL #AzureSQLDB' that start with a # character 
-- followed by one or more alphanumeric characters (A-Z, a-z, 0-9) or underscores (_).
SELECT *
    FROM REGEXP_MATCHES ('Learning #AzureSQL #AzureSQLDB', '#([A-Za-z0-9_]+)');

-- Return strings from ABC that match strings that start with the letter A followed by exactly two characters.
SELECT *
    FROM REGEXP_MATCHES ('ABC', '^(A)(..)$');

-- Return a table split for the quick brown fox jumps over the lazy dog.
SELECT *
    FROM REGEXP_SPLIT_TO_TABLE ('the quick brown fox jumps over the lazy dog', '\s+');

Practical example:

-- Return JSON object with one key
-- The following example returns a JSON object with one key and null value.
SELECT JSON_OBJECTAGG('key':NULL);

-- Construct JSON object from result set
-- The following example constructs a JSON object with three properties from a result set.
SELECT JSON_OBJECTAGG(c1:c2)
    FROM (
        VALUES('key1', 'c'), ('key2', 'b'), ('key3', 'a')
    ) AS t(c1, c2);

-- Return result with two columns
-- The following example returns a result with two columns. The first column contains the object_id value. The second column contains a JSON object where the key is the column name and value is the column_id.
SELECT TOP (5) c.object_id,
               JSON_OBJECTAGG(c.name:c.column_id) AS columns
    FROM sys.columns AS c
    GROUP BY c.object_id;

-- Return a JSON object as JSON type
-- The following example returns a JSON object as json type.
SELECT JSON_OBJECTAGG('a':1 RETURNING JSON);

-- Return aggregated result with four columns
-- The following example returns a result with four columns from a SELECT statement 
-- containing SUM and JSON_OBJECTAGG aggregates with GROUP BY GROUPING SETS. 
-- The first two columns return the id and type column value. The third column total_amount 
-- returns the value of SUM aggregate on the amount column. The fourth column json_total_name_amount 
-- returns the value of JSON_OBJECTAGG aggregate on the name and amount columns.
;WITH T AS (
    SELECT *
        FROM (
            VALUES (1, 'k1', 'a', 2), 
                   (1, 'k2', 'b', 3), 
                   (1, 'k3', 'b', 4), 
                   (2, 'j1', 'd', 7), 
                   (2, 'j2', 'd', 9)
        ) AS b(id, name, type, amount)
)
SELECT id,
       type,
       SUM(amount) AS total_amount,
       JSON_OBJECTAGG(name:amount) AS json_total_name_amount
    FROM T
    GROUP BY GROUPING SETS((id), (type), (id, type), ());

-- The following example returns an empty JSON array.
SELECT JSON_ARRAYAGG(NULL);

--The following example constructs a JSON array with three elements from a result set.
SELECT JSON_ARRAYAGG(c1)
    FROM (VALUES ('c'), ('b'), ('a')) AS t(c1);

-- The following example constructs a JSON array with three elements ordered by the value of the column.
SELECT JSON_ARRAYAGG( c1 ORDER BY c1)
    FROM (
        VALUES ('c'), ('b'), ('a')
    ) AS t(c1);

-- The following example returns a result with two columns. The first column contains the object_id value. 
-- The second column contains a JSON array containing the names of the columns. The columns in the 
-- JSON array are ordered based on the column_id value.
SELECT TOP(5) c.object_id, JSON_ARRAYAGG(c.name ORDER BY c.column_id) AS column_list
    FROM sys.columns AS c
    GROUP BY c.object_id;

-- The following example returns a result with four columns from a SELECT statement 
-- containing SUM and JSON_ARRAYAGG aggregates with GROUP BY GROUPING SETS. 
-- The first two columns return the id and type column value. The third column total_amount 
-- returns the value of SUM aggregate on the amount column. The fourth column json_total_amount 
-- returns the value of JSON_ARRAYAGG aggregate on the amount column.
; WITH T AS (
    SELECT *
        FROM (VALUES (1, 'k1', 'a', 2), 
                     (1, 'k2', 'b', 3), 
                     (1, 'k3', 'b', 4), 
                     (2, 'j1', 'd', 7), 
                     (2, 'j2', 'd', 9)) AS b(id, name, type, amount)
)
SELECT id,
       type,
       SUM(amount) AS total_amount,
       JSON_ARRAYAGG(amount) AS json_total_amount
    FROM T
    GROUP BY GROUPING SETS((id), (type), (id, type), ());

-- The following example returns a JSON array as json type.
SELECT JSON_ARRAYAGG(1 RETURNING JSON);

Practical example:

-- The following example compares two words and returns the 
-- EDIT_DISTANCE() value as a column, named Distance.
SELECT 'Colour' AS WordUK,
       'Color' AS WordUS,
       EDIT_DISTANCE('Colour', 'Color') AS Distance;
       
-- The following example compares two words and returns 
-- the EDIT_DISTANCE_SIMILARITY() value as a column, named Distance.
SELECT 'Colour' AS WordUK,
       'Color' AS WordUS,
       EDIT_DISTANCE_SIMILARITY('Colour', 'Color') AS Distance;       

-- The following example compares two words and returns the 
-- JARO_WINKLER_DISTANCE value as a column, named Distance.
SELECT 'Colour' AS WordUK,
       'Color' AS WordUS,
       JARO_WINKLER_DISTANCE('Colour', 'Color') AS Distance;

-- The following example compares two words and returns the 
-- JARO_WINKLER_SIMILARITY value as a column, named Similarity.
SELECT 'Colour' AS WordUK,
       'Color' AS WordUS,
       JARO_WINKLER_SIMILARITY('Colour', 'Color') AS Similarity;

Practical example:

-- Use UNISTR vs the NCHAR function
-- The following examples all use the UNISTR functions to perform the necessary conversion 
-- of the Unicode values into string character set, to display the Unicode character Smiling 
-- Face With Open Mouth. The database collation must be a UTF-8 collation if the input is of 
-- char or varchar data types.
SELECT N'Hello! ' + NCHAR(0xd83d) + NCHAR(0xde00);
SELECT UNISTR(N'Hello! \D83D\DE00');
SELECT UNISTR(N'Hello! \+01F603');

-- Use UNISTR function with user defined escape character
-- The following example uses the UNISTR function with a custom escape character to perform 
-- the necessary conversion of the Unicode into a string character set.
SELECT UNISTR(N'ABC#00C0#0181#0187', '#');

-- Use UNISTR function by combining string literals and Unicode code points
-- In the following example, UNISTR is used with a user-defined escape character ($) and a varchar 
-- data type with UTF-8 collation. It combines string literals with a Unicode codepoint value:
SELECT UNISTR('I $2665 Azure SQL.' COLLATE Latin1_General_100_CI_AS_KS_SC_UTF8, '$');

-- Use UNISTR function for characters beyond the UTF-8 limit
-- If you need to use a character set beyond UTF-8, you must convert the character sequence 
-- to UTF-8 using the COLLATE clause. Here's an example:
SELECT UNISTR('\306F\3044' COLLATE Latin1_General_100_BIN2_UTF8) AS Yes_in_Japanese_Hiragana;

-- Multiply rows together
-- The following example uses the PRODUCT function:
SELECT PRODUCT(CAST(pod.OrderQty AS DECIMAL(38, 3))) AS ProductOfPrices
        , COUNT(*)
    FROM Purchasing.PurchaseOrderDetail AS pod
    WHERE ModifiedDate BETWEEN '2014-07-01' AND '2015-05-26'
    GROUP BY pod.PurchaseOrderID

-- Use the OVER clause
-- The following example uses the PRODUCT function with the OVER clause to provide a rate of 
-- return on hypothetical financial instruments. The data is partitioned by finInstrument.
SELECT finInstrument,
       PRODUCT(1 + rateOfReturn) OVER (PARTITION BY finInstrument) AS CompoundedReturn
    FROM (VALUES (0.1626, 'instrumentA'),
                 (0.0483, 'instrumentB'),
                 (0.2689, 'instrumentC'),
                (-0.1944, 'instrumentA'),
                (0.2423, 'instrumentA')
    ) AS MyTable(rateOfReturn, finInstrument);

-- Get the current system date and time
SELECT SYSDATETIME(),
       SYSDATETIMEOFFSET(),
       SYSUTCDATETIME(),
       CURRENT_TIMESTAMP,
       GETDATE(),
       GETUTCDATE(),
       CURRENT_DATE;

-- Get the current system date
SELECT CONVERT (DATE, SYSDATETIME()),
       CONVERT (DATE, SYSDATETIMEOFFSET()),
       CONVERT (DATE, SYSUTCDATETIME()),
       CONVERT (DATE, CURRENT_TIMESTAMP),
       CONVERT (DATE, GETDATE()),
       CONVERT (DATE, GETUTCDATE()),
       CURRENT_DATE;

-- The following example shows how to return only a part of a character string. 
-- From the sys.databases table, this query returns the system database names in the first column, 
-- the first letter of the database in the second column, and the third and fourth characters 
-- in the final column.
SELECT name,
       SUBSTRING(name, 1, 1) AS Initial,
       SUBSTRING(name, 3, 2) AS ThirdAndFourthCharacters
    FROM sys.databases
    WHERE database_id < 5;

-- Use SUBSTRING without a length argument to find replacement parts in AdventureWorks2022 inventory
SELECT [ProductDescriptionID],
       [Description],
       SUBSTRING([Description], LEN('Replacement') + 1) AS [Replacement-Part]
    FROM [Production].[ProductDescription]
    WHERE [Description] LIKE 'Replacement%';

-- The following examples illustrate the use of various datepart options:
DECLARE @d datetime2 = '2021-12-08 11:30:15.1234567';
SELECT 'Year', DATETRUNC(year, @d);
SELECT 'Quarter', DATETRUNC(quarter, @d);
SELECT 'Month', DATETRUNC(month, @d);
SELECT 'Week', DATETRUNC(week, @d); -- Using the default DATEFIRST setting value of 7 (U.S. English)
SELECT 'Iso_week', DATETRUNC(iso_week, @d);
SELECT 'DayOfYear', DATETRUNC(dayofyear, @d);
SELECT 'Day', DATETRUNC(day, @d);
SELECT 'Hour', DATETRUNC(hour, @d);
SELECT 'Minute', DATETRUNC(minute, @d);
SELECT 'Second', DATETRUNC(second, @d);
SELECT 'Millisecond', DATETRUNC(millisecond, @d);
SELECT 'Microsecond', DATETRUNC(microsecond, @d);