DAY-4

Day 4 session on Scalar Functions in SQL, focusing on the most-used ones: LEN, UPPER, LOWER, GETDATE, and CONVERT. Each function is explained with real-world scenarios, followed by at least 4 practical examples per function.

Day 4 – Scalar Functions in SQL

Objective:

Learn how to use built-in scalar functions to manipulate and transform individual values in SQL queries.

Scalar Function 1: LEN()

Purpose:

Returns the number of characters in a string (excluding trailing spaces).

Real-life Use Cases:

• Validating password length
• Detecting short names or abbreviations
• Identifying incomplete data entries
• Trimming issues check (trailing spaces not counted)

Examples:

-- Example 1: Length of employee first names

SELECT FirstName, LEN(FirstName) AS NameLength FROM Employees;

 

-- Example 2: Find employees with short last names (≤4 characters)

SELECT * FROM Employees WHERE LEN(LastName) <= 4;

 

-- Example 3: Detect usernames with length more than 10

SELECT Username FROM Users WHERE LEN(Username) > 10;

 

-- Example 4: Sort employees by name length

SELECT FirstName FROM Employees ORDER BY LEN(FirstName) DESC;

 

Scalar Function 2: UPPER()

Purpose:

Converts a string to uppercase.

Real-life Use Cases:

• Ensuring case-insensitive comparison
• Preparing data for export in uniform case
• Matching user input without worrying about case
• Displaying names in all caps on badges or forms

Examples:

-- Example 1: Convert all last names to uppercase

SELECT UPPER(LastName) AS UpperLastName FROM Employees;

 

-- Example 2: Case-insensitive search

SELECT * FROM Employees WHERE UPPER(Department) = 'SALES';

 

-- Example 3: Uniform formatting for reports

SELECT EmployeeID, UPPER(FirstName + ' ' + LastName) AS FullNameCaps FROM Employees;

 

-- Example 4: Compare two names ignoring case

SELECT * FROM Employees WHERE UPPER(FirstName) = UPPER('john');

 

Scalar Function 3: LOWER()

Purpose:

Converts a string to lowercase.

Real-life Use Cases:

• Email comparison (emails are case-insensitive)
• Sanitizing input data
• Consistent storage in databases
• Case-insensitive joins or filters

Examples:

-- Example 1: Convert email addresses to lowercase

SELECT LOWER(Email) AS LowerEmail FROM Employees;

 

-- Example 2: Case-insensitive filtering

SELECT * FROM Employees WHERE LOWER(JobTitle) = 'intern';

 

-- Example 3: Standardize user IDs

SELECT LOWER(UserID) AS NormalizedID FROM Users;

 

-- Example 4: Create case-insensitive join

SELECT a.EmployeeID, b.Feedback FROM Employees a

JOIN Feedback b ON LOWER(a.Email) = LOWER(b.EmployeeEmail);

 

Scalar Function 4: GETDATE()

Purpose:

Returns the current system date and time.

Real-life Use Cases:

• Logging creation timestamps
• Filtering today's activity
• Calculating age or service duration
• Adding audit trails

Examples:

-- Example 1: Show current system date and time

SELECT GETDATE() AS CurrentDateTime;

 

-- Example 2: Get employees hired today

SELECT * FROM Employees WHERE HireDate = CAST(GETDATE() AS DATE);

 

-- Example 3: Insert a record with timestamp

INSERT INTO AuditLog (Action, ActionTime) VALUES ('User Login', GETDATE());

 

-- Example 4: Calculate service duration in years

SELECT FirstName, DATEDIFF(YEAR, HireDate, GETDATE()) AS YearsWorked FROM Employees;

 

Scalar Function 5: CONVERT()

Purpose:

Converts data types or formats (e.g., datetime to string, int to float, etc.)

Real-life Use Cases:

• Formatting dates for reports
• Changing number types (float to int, etc.)
• Storing datetime in string format
• Preparing CSV exports

 Examples:

-- Example 1: Convert hire date to different string format (style 103 = dd/mm/yyyy)

SELECT FirstName, CONVERT(VARCHAR, HireDate, 103) AS FormattedHireDate FROM Employees;

 

-- Example 2: Convert salary to float

SELECT FirstName, CONVERT(FLOAT, Salary) * 1.05 AS AdjustedSalary FROM Employees;

 

-- Example 3: Convert GETDATE() to just date (no time)

SELECT CONVERT(DATE, GETDATE()) AS TodayDate;

 

-- Example 4: Store formatted datetime as string

INSERT INTO ExportLog (ExportName, ExportedAt)

VALUES ('EmployeeExport', CONVERT(VARCHAR, GETDATE(), 120));  -- yyyy-mm-dd hh:mi:ss

Practice Tasks (Mini Exercises)

1. Display all employee names with their name lengths.
2. Find employees who were hired in the current year using GETDATE() and YEAR().
3. Show job titles in uppercase with a filter that ignores case.
4. Format all hire dates as 'dd/mm/yyyy' using CONVERT().
5. Return emails that are stored in inconsistent case (use LOWER() to fix it).   

Display all employee names with their name lengths

SELECT FirstName, LastName, LEN(FirstName) AS FirstNameLength, LEN(LastName) AS LastNameLength FROM Employees;

Find employees who were hired in the current year using GETDATE() and YEAR()

SELECT * FROM Employees WHERE YEAR(HireDate) = YEAR(GETDATE());

Show job titles in uppercase with a filter that ignores case

SELECT FirstName, LastName, UPPER(JobTitle) AS JobTitleCaps FROM Employees

WHERE UPPER(JobTitle) = UPPER('developer');  -- change 'developer' to any title to filter  

Format all hire dates as 'dd/mm/yyyy' using CONVERT()

SELECT  FirstName, LastName, CONVERT(VARCHAR, HireDate, 103) AS FormattedHireDate

FROM Employees;  

Return emails that are stored in inconsistent case (use LOWER() to fix it)

SELECT EmployeeID, Email AS OriginalEmail, LOWER(Email) AS NormalizedEmail FROM Employees;

Previous                                                                                                                                  Next