String Functions and Operators

StringFunctions.sql

-- Prints the sub-string located at the given start index for the given length
PRINT SUBSTRING('hello world', 6, 6);

-- Prints the sub-string located N characters to the RIGHT
PRINT RIGHT('hello world', 5);

-- Prints the sub-string located N characters to the LEFT
PRINT LEFT('hello world', 5);

-- Prints the amount of characters in the string
PRINT LEN('Hello World');

-- Prints the amount of bytes in the string
PRINT DATALENGTH('Hello World'); 

-- Prints the index of the first character in the expression to find
PRINT CHARINDEX('Ben', 'Itzik Ben-Gan');

-- Prints the index of the first character in the pattern match 
PRINT PATINDEX('%[0-9]%', 'abcd123efgh');

-- Replaces (and prints) all occurences of substring1 with substring2 
PRINT REPLACE('Hello World', 'Hello', 'Goodbye');

-- Prints the count of a given letter using REPLACE
PRINT LEN('Hello') - LEN(REPLACE('Hello', 'l', ''));

-- Replicates a string N times
PRINT REPLICATE('LOL ', 10);

-- Remotes the given substring from a string and inserts a new substring instead
PRINT STUFF('xyz', 2, 1, 'abc');

-- Converts (and prints) the given string to uper-case
PRINT UPPER('hoi');

-- Converts (and prints) the given string to lower-case
PRINT LOWER('HOI');

-- Removes leading or trailing spaces from the left
PRINT LTRIM('    Hello    ');

-- Removes leading or trailing spaces from the right
PRINT RTRIM('    Hello    ');

-- Combine LTRIM and RTRIM to remove leading or trailing spaces from both sides
PRINT LTRIM(RTRIM('    Hello    '));

-- Format the given input value as a character string based on a 
-- Microsoft .NET format string and optional culture. 
PRINT FORMAT(1759, '000000000000');