Single Row Functions in SQL Server

Single Row Functions in SQL Server

Overview of Single Row Functions

• A Single Row Function in SQL Server operates on a single row and returns a single value per row.
• Utilized in SQL statements like SELECT, WHERE, and ORDER BY to manipulate or retrieve specific data.

Types of Single Row Functions

String Functions

• Purpose: Manipulate character data.

• Functions:

  • UPPER(): Converts text to uppercase.

  • Example: SELECT UPPER('hello');

  • Output: HELLO

  • LOWER(): Converts text to lowercase.

  • Example: SELECT LOWER('HELLO');

  • Output: hello

  • LEN(): Returns string length (excluding trailing spaces).

  • Example: SELECT LEN('SQL Server');

  • Output: 10

  • SUBSTRING(): Extracts part of a string.

  • Example: SELECT SUBSTRING('Database', 2, 3);

  • Output: ata

  • REPLACE(): Replaces occurrences of a substring.

  • Example: SELECT REPLACE('SQL 2025', '2025', 'Server');

  • Output: SQL Server

  • LTRIM(): Removes leading spaces.

  • Example: SELECT LTRIM(' Hello');

  • Output: Hello

  • RTRIM(): Removes trailing spaces.

  • Example: SELECT RTRIM('Hello ');

  • Output: Hello

Mathematical Functions

• Purpose: Perform numerical calculations.

• Functions:

  • ABS(): Returns absolute value.

  • Example: SELECT ABS(-15);

  • Output: 15

  • CEILING(): Rounds up to the nearest integer.

  • Example: SELECT CEILING(4.2);

  • Output: 5

  • FLOOR(): Rounds down to the nearest integer.

  • Example: SELECT FLOOR(4.8);

  • Output: 4

  • ROUND(): Rounds a number to specific decimal places.

  • Example: SELECT ROUND(4.567, 2);

  • Output: 4.57

  • RAND(): Returns a random number between 0 and 1.

  • Example: SELECT RAND();

  • Output: varies (e.g., 0.8423)

Date & Time Functions

• Purpose: Work with date and time values.

• Functions:

  • GETDATE(): Returns current date and time.

  • Example: SELECT GETDATE();

  • Output: 2025-03-18 14:45:30.123

  • DATEADD(): Adds an interval to a date.

  • Example: SELECT DATEADD(DAY, 5, '2025-03-18');

  • Output: 2025-03-23

  • DATEDIFF(): Returns the difference between two dates.

  • Example: SELECT DATEDIFF(YEAR, '2000-01-01', '2025-03-18');

  • Output: 25

  • FORMAT(): Formats a date as a string.

  • Example: SELECT FORMAT(GETDATE(), 'yyyy-MM-dd');

  • Output: 2025-03-18

Conversion Functions

• Purpose: Convert data types.

• Functions:

  • CAST(): Converts a data type.

  • Example: SELECT CAST(123.45 AS INT);

  • Output: 123

  • CONVERT(): Converts with format options.

  • Example: SELECT CONVERT(VARCHAR, GETDATE(), 101);

  • Output: 03/18/2025

Practical Examples of Single Row Functions

Example: Masking Sensitive Data

• Bank Account Number Masking:

  • Query: SELECT REPLICATE('*', LEN('00703526899988') - 4) + RIGHT('00703526899988', 4) AS MaskedAccount;
  • Output: **********9998

• Email Address Masking:

  • Query: SELECT LEFT('john.doe@example.com', 1) + REPLICATE('*', CHARINDEX('@', 'john.doe@example.com') - 2) + SUBSTRING('john.doe@example.com', CHARINDEX('@', 'john.doe@example.com'), LEN('john.doe@example.com')) AS MaskedEmail;
  • Output: j*******@example.com

• CNIC Masking:

  • Query: SELECT LEFT('4220112345671', 5) + '-******-' + RIGHT('4220112345671', 1) AS MaskedCNIC;
  • Output: 42201-******-7

Example: Finding Customer Names

• Query: SELECT CustomerName FROM Customers WHERE LEN(CustomerName) > 10;
• Output: Muhammad Faizan, Syed Abdullah, Abdul Rehman

Example: Uppercase Employee Names

• Query: `SELECT UPPER(EmployeeName) AS UpperCaseName FROM Employees;
• Output: AHMED ALI ASAD ULLAH, HAFIZ USMAN

Example: Extracting Part of a String

• Query: SELECT CityName, SUBSTRING(CityName, 1, 5) AS ShortName FROM Cities;
• Output: Karachi Karac, Lahore Lahor, Faisalabad Faisa

Example: Calculating Absolute Salary Difference

• Query: SELECT ABS(Salary1 - Salary2) AS SalaryDifference FROM Employees;
• Output: 15000, 32000

Example: Generating Random Discounts

• Query: SELECT CAST(RAND() * 15 + 5 AS INT) AS DiscountPercentage;
• Output: varies, e.g., 12, 18

Example: Calculating Age

• Query: SELECT DATEDIFF(YEAR, '2002-01-05', GETDATE()) AS Age;
• Output: 23

Example: Formatting Salaries and Dates

• Query: SELECT EmployeeName, 'Salary: ' + CAST(Salary AS VARCHAR) AS SalaryReport FROM Employees;

• Output: Bilal Khan Salary: 85000

• Query: SELECT StudentName, CONVERT(VARCHAR, AdmissionDate, 105) AS FormattedAdmissionDate FROM Students;

• Output: Hina Khan 05-03-2025

Example: Handling NULL Values

• Replace NULLs:

  • Query: SELECT CustomerName, ISNULL(Address, 'Not Provided') AS Address FROM Customers;
  • Output: Nida Bashir Not Provided

• Get First Available Contact:

  • Query: SELECT CustomerName, COALESCE(Phone, Email, WhatsApp) AS PrimaryContact FROM Customers;
  • Output: Hassan Raza 03001234567

Example: Generating Unique IDs

• Query: SELECT NEWID() AS UniqueTransactionID;
• Output: 3F2504E0-4F89-41D3-9A0C-0305E82C3301