Tutorials
Search…
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
Copy link