20.1. SQL Part 2 - String and Date Functions¶
Use the examples and links in the following tables to get familiar with the various SQL functions.
126.96.36.199. Part 1: String Functions.¶
|RTRIM.||Removes whitespaces from right of last character.||
||Too many extra spaces.|
|LTRIM.||Removes whitespaces from left of first character.||
|LEFT.||Returns length of characters starting at provided character index and moving left.||
|RIGHT.||Returns length of characters starting at provided character index and moving right.||
|LEN.||Returns the length of a string based on characters, not including trailing spaces.||
|DATALENGTH.||Returns the length of a string based on bytes, not including trailing spaces.||
|CHARINDEX.||Can use to find specific character within a string. Returns the index location.||
|SUBSTRING.||Returns part of a string. First number is starting index location and second number is ending index location.||
|REVERSE.||Returns the string backwards.||
|UPPER.||Returns a string either in all upper cases.||
|LOWER.||Returns a string either in all lower cases.||
|REPLACE.||Replaces part of a string using provided patterns.||
|In this example, the code replaces the ‘ea’ with ‘ee’ if the ‘ea’ pattern is present.||
|CONCAT.||Combine strings together.||
|Good for working with null values as seen in example 2.||
|CONCAT_WS.||Combines strings together with a specified separator value. The separator can be anything you want.||
|Works with NULL values like
||Alyce - Frey|
|STUFF.||Inserts string into another string. The first number indicates where to insert the new chars.||
||PuAdd Charsmpkin Pie|
|The second number indicates how many original characters will be deleted upon insertion.||
||PuDelete Chars Pie|
188.8.131.52. Part 2: Date and Time Data Types and Functions.¶
|GETDATE.||Returns a timestamp that includes the date and time of the server.||
|SYSDATETIME.||Returns a timestamp that includes the date and time of the server.
|DATEADD.||Adds a time period to a date. In this example, we are increasing the month value from 02 (February) to 03(March).||
|DATEDIFF.||Calculates and returns the difference of a date part between an end date and a start date.||
|The first example is comparing the difference between days while the second is comparing months.||
|The third example is comparing years.||
|DATENAME.||Returns a string representing the desired date part.||
|DATEPART.||Returns an integer representing the desired date part.||
|DAY, MONTH, YEAR.||Each part of a date.||
|CONVERT.||Paired with GETDATE to convert other data types into dates and times based on the desired format.||
||Jan 5 2022 7:25PM|
|These examples convert the server date into various formats. The first example is the default format, the second example is format number 7.||
||Jan 05, 22|
|FORMAT.||Used to set the format or reformat dates. This example is using the following date information: Jan 5, 2022 7:38PM. The first example the server date is formatted date, month, year. The time is not requested, so it is not returned.||
|The second example requests only the time in hours and minutes. Note that ‘MM’ is used for months and ‘mm’ is used for minutes.||
184.108.40.206. Part 3: More Aggregations and Miscellany.¶
|SUM.||Returns sum of all values or DISTINCT values.||
|MAX.||Returns the highest value.||
|MIN.||Returns the lowest value.||
|IS NULL.||Determines whether value is null, often used as a condition with
||See examples below.|
In example 1, we asked for the TOP 5 rows, but only 3 qualified and were returned.
1 2 3
SELECT TOP 5 title, isbn, original_publication_year FROM BooksDB.dbo.books WHERE isbn IS NULL AND original_publication_year IS NULL;
|1||BookRags Summary: A Storm of Swords||NULL||NULL|
|2||A Shade of Blood (A Shade of Vampire, #2)||NULL||NULL|
1 2 3
SELECT title, original_title FROM BooksDB.dbo.books WHERE authors LIKE 'Sophocles%' AND original_title IS NULL;
|1||The Oedipus Cycle: Oedipus Rex/Oedipus at Colonus/Antigone (The Theban Plays, #1-3)NULL||NULL|
|IS NOT NULL.||Determines whether value is not null, often used as a condition with
||See examples below.|
1 2 3
SELECT TOP 5 title, isbn, original_publication_year FROM BooksDB.dbo.books WHERE isbn IS NOT NULL AND original_publication_year IS NOT NULL;
|1||The Hunger Games (The Hunger Games, #1)||439023483||2008|
|2||Harry Potter and the Sorcerer’s Stone (Harry Potter, #1)||439554934||1997|
|3||Twilight (Twilight, #1)||316015849||2005|
|4||To Kill a Mockingbird||61120081||1960|
|5||The Great Gatsby||743273567||1925|
1 2 3
SELECT title, original_title FROM BooksDB.dbo.books WHERE authors LIKE 'Sophocles%' AND original_title IS NOT NULL;
|1||Oedipus Rex (The Theban Plays, #1)||Οἰδίπους Τύραννος|
|2||Antigone (The Theban Plays, #3)||Ἀντιγόνη|
|ISNULL.||Replaces a specific null value.||See example below.|
Note the use of an alias in the example below.
1 2 3
SELECT original_title, ISNULL(original_title, title) AS UpdatedOriginalTitle FROM BooksDB.dbo.books WHERE authors LIKE 'Sophocles%';
|1||Οἰδίπους Τύραννος||Οἰδίπους Τύραννος|
|3||NULL||The Oedipus Cycle: Oedipus Rex/Oedipus at Colonus/Antigone (The Theban Plays, #1-3)|
|COALESCE.||Returns the first value that is not null. Can also be used to test multiple expressions unlike
||See examples below.|
SELECT COALESCE(NULL, 'cat', 'bird'); SELECT COALESCE('cat', NULL, 'bird');
1 2 3
SELECT COALESCE(original_title, title) + ' by ' + authors AS 'Reading List' FROM BooksDB.dbo.books WHERE authors LIKE 'Sophocles%';
|1||Οἰδίπους Τύραννος by Sophocles, J.E. Thomas|
|2||Ἀντιγόνη by Sophocles, J.E. Thomas|
|3||The Oedipus Cycle: Oedipus Rex/Oedipus at Colonus/Antigone (The Theban Plays, #1–3) by Sophocles, Dudley Fitts, Elena Bono, Robert Fitzgerald|
20.1.2. Check Your Understanding¶
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?
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?
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?
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?
Willow has a column in her table containing dates the library branches opened. She wants to compare them to today’s date. Which function would allow her to do that?
Willow has a table that contains dates library books are checked out. She uses the following function in her query:
WHERE DATEPART(MONTH, BorrowDate) = 05. What will this return?
- Rows of data where books were borrowed in the month of May
- Rows of data where books were borrowed on the 5th day of the month.
- Rows of data where books were borrowed in 2005.
- Rows of data where the same library user borrowed exactly 5 books.
Willow wants to have all the dates in her database in the same format of ‘month-day-year’. She used the following syntax:
FORMAT(ModifiedDate, 'mm/dd/yyyy') and received an error message. Why?
Francis has a database that contains information about car rentals. She wants to create a query that would return the oldest car the company owns. Which aggregation method would be a good fit for this query?
Francis wants to find the car with the highest mileage. Which aggregation method would be a good option for this query?
Francis wants to create a column that contains a complete address for each car rental office in her database. She has a column for street address, suite number, city, state, and zip code. After some quick EDA, she has discovered that every rental office has a street address, city, state, and zip code. She also discovered that only about 20% have suite numbers while the remaining 80% are null. What function would let her still join these elements into addresses without worrying about the null elements affecting the final output?
IS NOT NULL