Lesson 9. String Functions

If you work in analytics, you are going to be spending a lot of time manipulating strings. You are pretty much going to use ALL the string functions at some point.

This is an opinionated list of frequently used string functions.

Function	Explanation
CONCAT	Lets you combine string values.
LEN	Lets you count the number of characters a string has. Does not count trailing spaces.
LEFT	Lets you select a specified number of characters from the left.
RIGHT	Lets you select a specified number of characters from the right.
LTRIM	Lets you remove spaces from the start of a string.
RTRIM	Lets you remove spaces from the end of a string.
REPLACE	Lets you replace a string in a string with another string.
SUBSTRING	Lets you select a specified number of characters from any starting point in a string.

Examples

Example Of String Function Usage

Here is example usage of the functions above. Some functions like RTRIM are hard to determine if anything is happening because the data in AdventureWorks is very clean.

Parameter values are 1 based meaning that the index starts at 1.

USE AdventureWorks2016

SELECT
FirstName,
MiddleName,
LastName,
CONCAT(FirstName,MiddleName,LastName) AS SmooshedTogether
FROM Person.Person

SELECT 
Description,
LEN(Description) AS CountOfCharactersInDescription
FROM Production.ProductDescription

SELECT 
Description,
LEFT(Description,20) AS Left20Chars
FROM Production.ProductDescription

SELECT 
Description,
RIGHT(Description,20) AS Right20Chars
FROM Production.ProductDescription

SELECT 
Description,
LTRIM(Description) AS LeadingSpaceRemoved
FROM Production.ProductDescription

SELECT 
Description,
RTRIM(Description) AS TrailingSpaceRemoved
FROM Production.ProductDescription

SELECT 
Description,
REPLACE(Description,'large', 'small') AS DescriptionChanged
FROM Production.ProductDescription
WHERE ProductDescriptionID = 4

SELECT 
Description,
SUBSTRING(Description, 10, 15) AS RandomSubstringSelection
FROM Production.ProductDescription