Comment on page
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. |
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