SQL Server : Get Some Characters from a String Value

At least, there are 3 options for how to get some characters from a text string in SQL Server. We can use the LEFT(), RIGHT(), or SUBSTRING() functions depending on our needs.

For details, let's discuss them one by one.

LEFT() Function

If we need to extract some characters from the beginning of a text string (from the left) then the function we use is the LEFT() function.

Syntax:

LEFT(string, number_of_chars)

string: text string to extract
number_of_chars: the number of characters to extract

e.g. we will take the first 5 characters of the following text.

DECLARE @String VARCHAR(50) = 'Learn to be happy right now!'

SELECT LEFT(@String, 5)

RIGHT() Function

If what is needed is to take some characters from the end of a text string (from the right) then the function we use is the RIGHT() function.

Syntax:

RIGHT(string, number_of_chars)

string: text string to extract
number_of_chars: the number of characters to extract (from the right)

e.g. we will take the last 7 characters of the following text.

DECLARE @String VARCHAR(50) = 'Learn to be happy right now!'

SELECT RIGHT(@String, 7)

SUBSTRING() Function

The SUBSTRING() function is also useful for extracting some characters from a text string, and we can specify a starting point for retrieving data. So if you want to take some characters in the middle, the choice is SUBSTRING().

Syntax:

SUBSTRING(string, start, length)

string: text string to extract
start: starting position. The first position in string is 1.
length: The number of characters to extract. Must be a positive number ( > 0 )

e.g: we will take 5 characters starting from the 7th position of the following text.

DECLARE @String VARCHAR(50) = 'Learn to be happy right now!'

SELECT SUBSTRING(@String, 7,5)

If we look further this SUBSTRING() function can also replace the role of the LEFT() and RIGHT() functions. But of course in a more complicated way.

DECLARE @String VARCHAR(50) = 'Learn to be happy right now!'

SELECT SUBSTRING(@String, 1,5) [Left], SUBSTRING(@String, LEN(@String)-6,7) [Right]