T-SQL Substring

Introduction to T-SQL Substring

T-SQL substring is the function that acknowledges the user to extract a substring from the provided string as per the user’s requirement so it can derive the string which has a defined length that can begin from the provided location. The main motive of the Substring() function is to give back a particular section of the string. It accepts three arguments, an expression which is the origin of the string in which we can get a substring, starting position that can decide the location in the expression from where the new string can begin, and total length is the anticipated length of the resultant substring.

What is T-SQL substring?

The Substring() is the function in T-SQL that can authorize a user to obtain a substring from the provided string group as per the requirement of the user. The obtained substring can have a particular length and it can begin from a given location in an input string, hence we can say that the substring can give back a particular section of the string,

Syntax for substring():

SUBSTRING(Expression, Starting Position, Total length)

Where,

• Expression- It can be any character, binary, or text, which can be the origin of a string in which we can get the substring as per the requirement.
• Starting position: It can dictate the position in expression through which the new string will begin.
• Total length: It is the total anticipated length of the resultant sub-string which can begin from starting position.

How to use T-SQL substring?

Let us see the examples to understand how to use T-SQL substring. If we have a column string and we have to gain substring from it,

Example #1

Let us consider if we have an ‘employee’ table having a column ‘email’ and we to show the first 6 characters of every email,

The email column of the table:

Code:

SELECT email, SUBSTRING (email, 1, 6) AS substring FROM employee;

Output:

In the above example, we have used the ‘SUBSTRING’ function in which the first argument is the string and the second argument is the index of the character from which the string has begun, and the third is the length that we require so it will give the string having email column which having first six characters of the given string.

Example #2

Let us see how to show the substring between index 3 and 7,

Code:

SELECT email, SUBSTRING (email, 3, 7) AS substring FROM employee;

Output:

In the above example, we have used the SUBSTRING () function, and we have used second argument 3 because we have to begin index from 3 with length 7 so the (end_index – start_index + 1) formula has been applied.

Example #3

Let us see how to show the substring that begins at ‘@’ and ends at the end of the string without index and length,

Code:

SELECT email, SUBSTRING (email, CHARINDEX ('@', email), LEN (email) – CHARINDEX ('@', email) + 1) AS substring FROM employee;

Output:

In the above example, we have used the Substring() function in which we have to search for a special character so for that, we have used the ‘CHARINDEX(character, column)’ function in which the character is the special character in which the substring has begun and argument will be the column from which we can gain substring, and the third argument of SUBSTRING() function is the length of the substring which we can determine it by using CHARINDEX() and LEN() function which we can perform it by subtracting index from the length of the column and adding one as given below,

“LEN (email) – CHARINDEX ('@', email) + 1”

and we can get the substring which will not end at the end of the string but at the special character for example previous to the ‘.’,

“SELECT email, SUBSTRING (email, CHARINDEX ('@', email), CHARINDEX ('.', email) – CHARINDEX ('@', email)) AS substring FROM employee;”

Output:

In which CHARINDEX (‘.’, email) – CHARINDEX (‘@’, email) can compute the length of the substring.

T-SQL substring Types

Let us see the types of the substring in T-SQL, there are three main types of it,

1. Character data type: If the expression can have char data type then it gives back a character data and has a specified expression as char/varchar/text.
2. Binary data type: If the expression carried a binary data type then it will return the binary data in which the expression will be, nchar/nvarchar/ntext.
3. String data type: If the specified expression carried exception as binary/varbinary/image then it will give back a string type of data.

Examples:

• Utilizing SUBSTRING with a character string:

Let us see an example that gives back only a section of a character string, it will give back the system database name from sys. database in the first column of the table, the first letter of the database name in the second column, and the fourth character in the third column respectively,

“SELECT name, SUBSTRING(name, 1, 1) AS Initial ,SUBSTRING(name, 3, 2) AS SecondAndThirdCharacters FROM sys.databases WHERE database_id < 5;”
WHERE database_id < 5;”

Output will be:

name	Initial	SecondAndThirdCharacter
master	m	as
tempdb	t	em
model	m	od
msdb	m	sd

• Let us see how to show the second, third, and fourth character of a constant string “ijklmn”.

Code:

“SELECT x= SUBSTRING (‘ijklmn’, 2, 3)”

Output will be:

• Utilizing SUBSTRING with text, ntext, and image data:

Let us see the example of how to give back the first 10 characters from every given text and image data column in the stud_id table of the stud database the text data can be given back as varchar, and image data will return as varbinary,

“USE stud;

SELECT stu_id, SUBSTRING (logo, 1, 10) AS logo, SUBSTRING(pr_info, 1, 10) AS pr_info FROM stu_info WHERE stu_id = '1756';”

Output will look like this:

Conclusion

In this article, we conclude that T-SQL substring has been used to give back a particular section of a string with a specified length which has compulsory three arguments, which also discussed how to use it and its types, so this article is helpful to understand the concept of T-SQL substring in detail.

Recommended Articles

This is a guide to T-SQL Substring. Here we discuss the Definition, Introduction, how to create, and examples with code implementation. You may also have a look at the following articles to learn more –

1. JDBC MySQL Driver
2. PL/SQL Raise Exception
3. PostgreSQL Update
4. PostgreSQL Backup