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.
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
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
3. LOWER() Function
The LOWER() function converts alphabetic characters in a string to lowercase.
Syntax
LOWER(string)
Example
SELECT LOWER('CIIT TRAINING INSTITUTE');
Output
4. LTRIM() Function
The LTRIM() function removes spaces from the beginning of a string.
Syntax
LTRIM(string)
Example
SELECT LTRIM(' SQL Server');
Result
5. RTRIM() Function
The RTRIM() function removes spaces from the end of a string.
Syntax
RTRIM(string)
Example
SELECT RTRIM('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);
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);
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);
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');
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');
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');
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.