Day 4 SQL Mastery: SUBSTRING, DATEPART, & Date Formatting
"Welcome to Day 4 of the '15 Days of SQL: The Complete SQL Masterclass 2024'! In this video, we dive into powerful SQL functions like SUBSTRING, DATEPART, FORMAT, and CONVERT. Learn how to extract and manipulate strings, work with date parts, and format dates in SQL Server. We'll also explore practical examples like email masking to ensure data privacy. Whether you're preparing for interviews or enhancing your SQL skills, this tutorial will help you master these essential functions.
Key Takeaways:
Extracting substrings from strings with SUBSTRING.
Understanding and using DATEPART to break down date and time.
Formatting dates with FORMAT and CONVERT.
Practical example: Masking email addresses.
Don't forget to like, subscribe, and hit the notification bell to stay updated with more SQL tutorials in this series!"
Additional Inputs:
Consider including a section at the end of the video where you discuss potential real-world use cases for these functions, such as in reporting, data analysis, or privacy protection.
You could also show how these functions can be combined with other SQL queries for more complex data manipulation. For example, combining SUBSTRING, DATEPART, and FORMAT in a report generation scenario.
This structure and these elements should make your Day 4 video both informative and engaging for your audience.
Day 1: SQL Basics - Master SELECT, ORDER BY, DISTINCT, LIMIT, and COUNT
• Day 1: SQL Basics - Master SELECT, ORDER B...
Day 2: Advanced SQL Queries – Filtering, Logical Operators, and Aggregates
• Day 2: Advanced SQL Queries – Filtering, L...
Day 3 SQL Masterclass : Aggregation Functions, GROUP BY, and HAVING Clauses Explained
• Day 3 SQL Masterclass : Aggregation Functi...
--Day4
drop table customer
CREATE TABLE customer (
customer_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100)
);
INSERT INTO customer (customer_id, first_name, last_name, email)
VALUES
(1, 'Adam', 'Smith', '[email protected]'),
(2, 'Emily', 'Jones', '[email protected]'),
(3, 'John', 'Doe', '[email protected]');
drop table rental
CREATE TABLE rental (
rental_id INT PRIMARY KEY,
customer_id INT,
rental_date DATETIME,
return_date DATETIME
);
INSERT INTO rental (rental_id, customer_id, rental_date, return_date)
VALUES
(1, 1, '2023-07-15 14:30:00', '2023-07-18 12:45:00'),
(2, 2, '2023-08-05 10:00:00', '2023-08-07 09:00:00'),
(3, 3, '2023-08-20 16:00:00', '2023-08-22 11:30:00');
SUBSTRING
✓ Used to EXTRACT a SUBSTRING from a string
SUBSTRING(expression, start, length)
select * from customer
--Extract a specific number of characters starting from a specific position:
select *,substring(email,2,3) as result from customer
-- This extracts 3 characters from the email column, starting from the 2nd character.
--Extract from a starting position without specifying a length:
select *,SUBSTRING(email,2,len(email)) as result from customer
--This extracts all characters from the email column, starting from the 2nd character to the end.
--Extract using POSITION to find the start point:
select *,substring(email,CHARINDEX('.',email)+1,3) as result from customer
--This finds the position of the first period (.) in the email column and then extracts the next 3 characters
--starting from that position.
DATEPART
✓ Used to DATEPART parts of timestamp/date
SELECT DATEPART(YEAR, some_date_column) AS year_part,
DATEPART(MONTH, some_date_column) AS month_part,
DATEPART(DAY, some_date_column) AS day_part
FROM some_table;
--Extracting Year, Month, Day, and Hour from rental_date:
select rental_id,rental_date,
DATEPART(YEAR, rental_date) AS year_part,
DATEPART(MONTH, rental_date) AS month_part,
DATEPART(DAY, rental_date) AS day_part,
DATEPART(HOUR, rental_date) AS hour_part
from rental
--Calculating the Week of the Year and Quarter:
select rental_id,rental_date,
DATEPART(WEEK, rental_date) AS weekofyear,
DATEPART(QUARTER, rental_date) AS quarter_part
from rental
--Combining DATEPART and SUBSTRING:
select rental_id,rental_date,
SUBSTRING(convert(varchar,DATEPART(YEAR, rental_date)),3,2) as yearshort
from rental
--Using SUBSTRING to Mask Emails:
select
customer_id,
concat(SUBSTRING(email,1,2),'*****',SUBSTRING(email,CHARINDEX('@',email),len(email))) as Maskedemail
from customer
--Formatting Dates and Numbers in SQL Server
select
rental_id,rental_date,
format(rental_date,'MM-yyyy') as Formatteddate
from rental
--Using CONVERT to Format Dates
select
rental_id,rental_date,
convert(varchar,rental_date,101) as Formatteddate
from rental
#SQLMasterclass
#SQLSUBSTRING
#SQLDATEPART
#SQLDate Formatting
#SQLTutorial
#DataMaskingSQL
#SQLFunctions
#SQLQueryExamples