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.

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

Last updated