This article describes how to use the T-SQL String functions in SQL Server database. String functions are: charindex, concat, replace, ltrim, rtrim, left, right, len, upper, lower, substring, patindex.

SQL Server functions are used to manipulate data and return the results of that manipulation. Functions do much more than just provide a way to perform calculations on data; they can also be used to manipulate strings in SQL Server.

In this article, we'll look at how you can use string functions in your SQL queries to manipulate and extract information from character strings.

What are string functions

SQL Server offers a variety of built-in functions that can be used to manipulate strings. Let's take a look at some of the most commonly used string functions in SQL Server.

CHARINDEX

The string function CHARINDEX returns the position of a substring in a string.

SELECT CHARINDEX('sql', 'learn about sql functions');
SELECT CHARINDEX('functions', 'learn about sql functions', 13);

CONCAT

The CONCAT is a string function and has the role of concatenating two or more strings.

SELECT CONCAT ('Learn', ' - ','SQL');
SELECT CONCAT( 'Learn', NULL, 'SQL' );

LEN

One of the most basic string functions is the LEN function, which returns the length of a string. For example, the following query returns the length of the string 'SQL Server':

SELECT LEN('SQL Server');

SUBSTRING

Another common string function is the SUBSTRING function, which allows you to extract a substring from an expression. For example, the following query extracts the first 5 characters from the string 'SQL Server':

SELECT SUBSTRING('SQL Server', 1, 5);
SELECT SUBSTRING('SQL Server', 1, 3);

PATINDEX

The string function PATINDEX returns the position of a pattern in a string.

SELECT PATINDEX ( '%sql%', 'SQL is a database language');
SELECT PATINDEX ( '%database%', 'SQL is a database language');

REPLACE

The REPLACE function replaces all occurrences of a substring within a string, with a new substring.

SELECT REPLACE('abc 123 test','123','789');
SELECT REPLACE('abc 123 test','123','xyz');
SELECT REPLACE('abc 123 test','abc 123','sql');

LOWER

The LOWER function converts a string to lowercase.

SELECT LOWER('test SQL');
SELECT LOWER('TEST SQL');

UPPER

The UPPER function converts a string to uppercase.

SELECT UPPER('learn about SQL');
SELECT UPPER('test 123');