20.1. SQL Part 2 - String Functions

20.1.1. String Functions

Function

Definition

Syntax

Value Returned

RTRIM.

Removes whitespaces from right of last character.

SELECT RTRIM('Too many trailing spaces.          ');

Too many extra spaces.

LTRIM.

Removes whitespaces from left of first character.

SELECT LTRIM('         Leading spaces');

Leading spaces

LEFT.

Returns length of characters starting at provided character index and moving left.

SELECT LEFT('hello there!', 7);

hello t

RIGHT.

Returns length of characters starting at provided character index and moving right.

SELECT RIGHT('hello there!', 3);

re!

LEN.

Returns the length of a string based on characters, not including trailing spaces.

SELECT LEN('hello there!'');

12

DATALENGTH.

Returns the length of a string based on bytes, not including trailing spaces.

SELECT DATALENGTH('hello there!'');

12

CHARINDEX.

Can use to find specific character within a string. Returns the index location.

SELECT CHARINDEX('log', 'catalogue');

5

SUBSTRING.

Returns part of a string. First number is starting index location and second number is ending index location.

SELECT SUBSTRING('Strings are fun!', 4, 9);

ings are

REVERSE.

Returns the string backwards.

SELECT REVERSE('Data Analysis');

sisylanA ataD

UPPER.

Returns a string either in all upper cases.

SELECT UPPER('taco');

TACO

LOWER.

Returns a string either in all lower cases.

SELECT LOWER('tACO');

taco

REPLACE.

Replaces part of a string using provided patterns.

SELECT REPLACE('Beach Streat', 'ea', 'ee');

Beech Street

In this example, the code replaces the ‘ea’ with ‘ee’ if the ‘ea’ pattern is present.

SELECT REPLACE('Read', 'ea', 'ee');

Reed

CONCAT.

Combine strings together.

SELECT CONCAT('Alyce','Cat', 'Frey');

AlyceCatFrey

Good for working with null values as seen in example 2.

SELECT CONCAT('Alyce', NULL, 'Frey');

AlyceFrey

CONCAT_WS.

Combines strings together with a specified separator value. The separator can be anything you want.

SELECT CONCAT_WS(' =::= ',  'Alyce', 'Frey');

Alyce=::=Frey

Works with NULL values like CONCAT.

SELECT CONCAT_WS('  -  ',  'Alyce', NULL, 'Frey');

Alyce - Frey

STUFF.

Inserts string into another string. The first number indicates where to insert the new chars.

SELECT STUFF('Pumpkin Pie', 3, 0, 'Add Chars');

PuAdd Charsmpkin Pie

The second number indicates how many original characters will be deleted upon insertion.

SELECT STUFF('Pumpkin Pie', 3, 5, 'Delete Chars');

PuDelete Chars Pie

20.1.2. Check Your Understanding

Question

Alyce is working with a dataset that contains information about local businesses. They want to create a query that returns titles of local businesses that contain “&”. Which string function should they use?

  1. LEN

  2. STUFF

  3. CHARINDEX

  4. RTRIM

Question

Alyce wants to create consistency in the formatting of the state abbreviations of the local business addresses contained in their dataset. What string function could help with this?

  1. REVERSE

  2. UPPER

  3. CONCAT

  4. LEFT

Question

Alyce’s dataset breaks down the addresses of local businesses into the following columns: "Street", "City", "State", and "Zipcode". They would like to create a column that has all items joined to return a complete address in a single column. Which function would best help with this?

  1. CONCAT or CONCAT_WS or STUFF

  2. LOWER

  3. REPLACE

  4. LEFT

Question

Alyce is working with a column of phone numbers. They only need the 7 digit number, not the area code. However, some of the numbers include the area code, some include the country code and area code, and some only include the seven-digit phone number. Which function could help them select the 7 digits that she needs?

  1. CONCAT_WS

  2. CHARINDEX

  3. RIGHT

  4. RTRIM