Master SQL Server From Scratch

Clear, interactive, and structured SQL Server lessons designed for beginners and developers.

COURSE SYLLABUS
1. Introduction to SQL Server
Introduction to SQL Server SQL Server Installation SQL Server Fundamentals
2. Data Types
Data Types Numeric Data Types Character & String Data Types Date & Time Data Types Other SQL Server Data Types
3. Database & Tables
Database & Tables Introduction Creating a Database Creating Tables Table Structure Altering Tables Dropping Tables
4. Constraints
Constraints Introduction PRIMARY KEY FOREIGN KEY UNIQUE Constraint NOT NULL & DEFAULT CHECK Constraint
5. SQL Basics
SQL Basics Introduction SQL Commands SQL Command Categories SELECT Statement INSERT, UPDATE & DELETE
6. CRUD Operations
CRUD Operations Introduction INSERT Operation SELECT Operation UPDATE Operation DELETE Operation
7. SQL Queries
Queries Introduction WHERE Clause ORDER BY Clause DISTINCT LIKE Operator IN, BETWEEN & IS NULL
8. Joins
Joins Introduction INNER JOIN LEFT JOIN RIGHT JOIN FULL OUTER JOIN CROSS JOIN
9. SQL Functions
SQL Functions Introduction String Functions Numeric Functions Date & Time Functions Conversion Functions NULL Functions
10. Grouping & Aggregate Functions
GROUP BY Aggregate Functions COUNT() SUM() AVG() MIN() & MAX()
11. Subqueries
Subqueries Introduction Single-Row Subquery Multiple-Row Subquery Correlated Subquery Subquery with DML
12. Views
SQL Views Introduction Creating a View Updating a View Altering a View Dropping a View
13. Stored Procedures
Stored Procedures Introduction Creating a Stored Procedure Input Parameters Output Parameters Executing & Modifying Stored Procedures
14. Triggers
Triggers Introduction AFTER INSERT Trigger AFTER UPDATE Trigger AFTER DELETE Trigger INSTEAD OF Trigger
15. Transactions
Transactions Introduction COMMIT ROLLBACK SAVEPOINT Transaction with TRY...CATCH
16. Indexes
Indexes Introduction Clustered Index Non-Clustered Index Creating & Dropping Indexes Index Performance
17. Common Table Expressions
CTE Introduction Simple CTE CTE with JOIN CTE with Aggregate Functions Recursive CTE
18. Window Functions
Window Functions Introduction ROW_NUMBER() RANK() and DENSE_RANK() LEAD() and LAG() PARTITION BY
19. Advanced SQL
Advanced SQL Introduction Temporary Tables Table Variables Dynamic SQL Error Handling Performance Optimization

SQL Server String Functions

String Functions in SQL Server are used to work with, manipulate, search, format and transform text or character data.

What are String Functions?

String functions are built-in SQL Server functions that perform operations on character and text values.

They are commonly used when we need to change the case of text, find the length of a string, extract a part of a string, replace text, combine strings or search for a particular character or word.

Example:

If a database contains a student's name such as "Rahul Sharma", String Functions can be used to find its length, extract "Rahul", convert it to uppercase, or replace part of the name.

Common String Functions

Function Purpose Example
LEN() Returns the number of characters in a string. LEN('Hello')
UPPER() Converts text into uppercase. UPPER('hello')
LOWER() Converts text into lowercase. LOWER('HELLO')
LTRIM() Removes leading spaces from a string. LTRIM(' Hello')
RTRIM() Removes trailing spaces from a string. RTRIM('Hello ')
LEFT() Returns a specified number of characters from the left side. LEFT('Hello', 2)
RIGHT() Returns a specified number of characters from the right side. RIGHT('Hello', 2)
SUBSTRING() Extracts a part of a string. SUBSTRING('Hello World', 1, 5)
REPLACE() Replaces one part of a string with another. REPLACE('Hello SQL', 'SQL', 'World')
CHARINDEX() Finds the starting position of a substring. CHARINDEX('SQL', 'Learn SQL')
CONCAT() Combines two or more strings. CONCAT('Hello', ' ', 'World')

1. LEN() Function

The LEN() function returns the number of characters in a string.

Syntax
LEN(string)
Example
SELECT LEN('CIIT');
Output
Output: 4

The word CIIT contains four characters, so LEN() returns 4.

2. UPPER() Function

The UPPER() function converts all alphabetic characters in a string to uppercase.

Syntax
UPPER(string)
Example
SELECT UPPER('ciit training institute');
Output
Output: CIIT TRAINING INSTITUTE

3. LOWER() Function

The LOWER() function converts alphabetic characters in a string to lowercase.

Syntax
LOWER(string)
Example
SELECT LOWER('CIIT TRAINING INSTITUTE');
Output
Output: ciit training institute

4. LTRIM() Function

The LTRIM() function removes spaces from the beginning of a string.

Syntax
LTRIM(string)
Example
SELECT LTRIM('   SQL Server');
Result
Output: SQL Server

5. RTRIM() Function

The RTRIM() function removes spaces from the end of a string.

Syntax
RTRIM(string)
Example
SELECT RTRIM('SQL Server   ');
Output: SQL Server

6. LEFT() Function

The LEFT() function returns a specified number of characters from the left side of a string.

Syntax
LEFT(string, number_of_characters)
Example
SELECT LEFT('SQL Server', 3);
Output: SQL

Here, 3 characters are returned from the left side.

7. RIGHT() Function

The RIGHT() function returns a specified number of characters from the right side of a string.

Syntax
RIGHT(string, number_of_characters)
Example
SELECT RIGHT('SQL Server', 6);
Output: Server

8. SUBSTRING() Function

The SUBSTRING() function extracts a specified number of characters from a string.

Syntax
SUBSTRING(string, start_position, length)
Example
SELECT SUBSTRING('SQL Server', 1, 3);
Output: SQL

The starting position is 1 and three characters are returned.

9. REPLACE() Function

The REPLACE() function replaces all occurrences of a specified string with another string.

Syntax
REPLACE(string, old_string, new_string)
Example
SELECT REPLACE('Welcome to Java', 'Java', 'SQL Server');
Output: Welcome to SQL Server

10. CHARINDEX() Function

The CHARINDEX() function searches for a substring inside another string and returns its starting position.

Syntax
CHARINDEX(search_string, expression)
Example
SELECT CHARINDEX('SQL', 'Learn SQL Server');
Output: 7

The word SQL starts at position 7 in Learn SQL Server.

11. CONCAT() Function

The CONCAT() function combines two or more values into a single string.

Syntax
CONCAT(value1, value2, value3, ...)
Example
SELECT CONCAT('CIIT', ' ', 'Training Institute');
Output: CIIT Training Institute

Practical Example with a Table

Suppose we have a Students table:

CREATE TABLE Students
(
    StudentId INT,
    StudentName VARCHAR(100)
);

Insert some sample records:

INSERT INTO Students
VALUES
(101, 'Rahul Sharma'),
(102, 'Priya Patil'),
(103, 'Amit Kumar');
Convert Student Names to Uppercase
SELECT
    StudentId,
    UPPER(StudentName) AS StudentName
FROM Students;
Find Name Length
SELECT
    StudentName,
    LEN(StudentName) AS NameLength
FROM Students;
Extract First Five Characters
SELECT
    StudentName,
    LEFT(StudentName, 5) AS FirstFiveCharacters
FROM Students;

When Do We Use String Functions?

  • When we need to change text into uppercase or lowercase.
  • When we need to find the length of text.
  • When we need to extract a part of a string.
  • When we need to replace specific text.
  • When we need to search for a word or character.
  • When we need to combine multiple text values.
  • When we need to clean or format text data.

Important Points

  • String functions are mainly used with character and text data.
  • LEN() is used to find string length.
  • UPPER() and LOWER() change the case of text.
  • LEFT() and RIGHT() extract characters from either side of a string.
  • SUBSTRING() extracts characters from a specified position.
  • REPLACE() replaces specified text.
  • CHARINDEX() searches for a substring.
  • CONCAT() combines multiple values into one string.

Summary

SQL Server String Functions are used to manipulate and process text data. In this topic, we learned commonly used functions such as LEN(), UPPER(), LOWER(), LTRIM(), RTRIM(), LEFT(), RIGHT(), SUBSTRING(), REPLACE(), CHARINDEX() and CONCAT(). These functions are very useful when working with names, addresses, descriptions, codes and other character-based data.