EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 600+ Courses All in One Bundle
  • Login
Home Software Development SQL Tutorials T-SQL Substring
Secondary Sidebar
Phishing and Pharming

Shell Scripting Interview Questions

Software Testing Interview Questions

What is JavaScript?

WordPress vs Wix

Web Services Interview Questions

T-SQL Substring

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,

Start Your Free Software Development Course

Web development, programming languages, Software testing & others

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:

11

Code:

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

Output:

dd

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:

ss

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:

T-SQL Substring jj

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:

T-SQL Substring hh

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:

ll

  • 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:

T-SQL Substring ff

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
Popular Course in this category
SQL Training Program (10 Courses, 8+ Projects)
  10 Online Courses |  8 Hands-on Projects |  80+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Java Tutorials
  • Python Tutorials
  • All Tutorials
Certification Courses
  • All Courses
  • Software Development Course - All in One Bundle
  • Become a Python Developer
  • Java Course
  • Become a Selenium Automation Tester
  • Become an IoT Developer
  • ASP.NET Course
  • VB.NET Course
  • PHP Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more