Page 189 - SQL

DECLARE @str1 VARCHAR(10) = 'Hello', @str2 VARCHAR(10) = 'FooBarBaz';
SELECT SUBSTRING(@str1, LEN(@str1) - 2, 3) --returns 'llo'
SELECT SUBSTRING(@str2, LEN(@str2) - 2, 3) --returns 'Baz'

Split

Splits a string expression using a character separator. Note that STRING_SPLIT() is a table-valued
function.

SELECT value FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ');

Result:

value
-----
Lorem
ipsum
dolor
sit
amet.

Stuff

Stuff a string into another, replacing 0 or more characters at a certain position.

Note: start position is 1-indexed (you start indexing at 1, not 0).

Syntax:

STUFF ( character_expression , start , length , replaceWith_expression )

Example:

SELECT STUFF('FooBarBaz', 4, 3, 'Hello') --returns 'FooHelloBaz'

Length

SQL Server

The LEN doesn't count the trailing space.

SELECT LEN('Hello') -- returns 5

SELECT LEN('Hello '); -- returns 5

The DATALENGTH counts the trailing space.

SELECT DATALENGTH('Hello') -- returns 5

https://riptutorial.com/ 171