Webinar Alert : Mastering  Manual and Automation Testing! - Reserve Your Free Seat Now

- SQL Server Blogs -

What is the Substring Function in the SQL? Example of SQL Server Substring

The process of data refactoring is common and vital in data mining operations. The SQL string functions are considered most suitable for data wrangling with the SQL server. One of the frequently used SQL string functions includes substring () to return the needed portion of a string.

We all agree that data stored in the database needs transformation and SQL string functions are taken most suitable for the data transformation. In this blog, we will discuss in detail SQL Substring function and how it is used with different database clauses like SELECT, WHERE, ORDER BY etc.

The Substring function in the SQL is used to return the portion of a string. Each database has its own way to execute this function.

  • MySQL – SUBSTR (), SUBSTRING ()
  • Oracle - SUBSTR ()
  • SQL Server - SUBSTRING ()

The basic syntax for SQL Substring is given as below –

  1. Expression: It could be character, binary, text, or image format.
  2. Start: It is an integer value that specifies the initial value which can be extracted by characters from the given expression. The first position always starts with the integer 1 and it could never be a negative integer value.
  3. Length: It is the optional parameter that returns the size of the string that you want to retrieve. If the length is not specified then it returns the rest of the string by default.

Things to Remember: It is clear from the discussion that start controls the initial position of the substring.

  • If the value of start is less than 1 then length is decremented by the corresponding value, if start value is 0, the value of length is diminished by 1, if the start value is 1, the value of length is diminished by 2.

The value of length controls the size of the substring.

  • If the value of length is 1 or greater than 1, the substring ends to the right of starting position.
  • If the value of length is greater than the remaining characters in a string, the substring is traversed to the right through the endpoint.
  • If the length is specified zero then the NULL value is returned.
  • If the length is the negative number then the cache will issue the 140-error.

Keep in mind that SQL function traversal is always from Left to Right.

Read: How to Use Like Operator in SQL Server?

Substring Function in the SQL

How to use SUBSTRING () and SUBSTR () in different ways with the SQL Server?

  • Substring function can be used to convert the floating point to integers by truncating the fractional portion.
  • Substring function is used to extract the substring from the beginning while SUBSTR () function is used to extract the substring either from the beginning or end. Keep in mind that both these functions handle arguments differently. Substring function can be used with characters stream data while SUBSTR () function cannot be used with characters stream data.
  • Substring function can be used as an ODBC scalar function or as an SQL general function.

Return Data Types

  • If the substring argument value is Null then it returns Null.
  • If the substring return data type is same that of string-expression then it allows substring function to handle multiple user-defined data types with the special encoding.
Specified expression Return type
char/varchar/text varchar
nchar/nvarchar/ntext nvarchar
binary/varbinary/image varbinary
  • If the return data type is not the same that of string-expression then substring return value could be anything like floating points or fractional numbers.

SQL Server Training & Certification

  • Detailed Coverage
  • Best-in-class Content
  • Prepared by Industry leaders
  • Latest Technology Covered

 SQL Substring – Real-world Scenarios In this section, we will discuss some real-world scenarios using SQL string functions. Let us get our hands dirty and dive deep to see more actions.

  • Using Substring with theSELECT” Clause

Here is the simple example that returns the portion of a string at the initial position 1 and extracts 5 characters from the starting point. The SQL substring function is quite useful when you want to extract characters to a certain limit.


SELECT firstname, SUBSTRING(firstname, 1, 5), lastname FROM Person.Person;
  • Using Substring with the “WHERE” Clause

Here, we will check how to return the selected portion of a character string.

Read: Difference Between SQLite and MySQL

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

From sys.databases table, this query returns the name of the database in the first column, the initial letter of the database in the second column, and the third or fourth characters in the final column. The result set is displayed as given below.

name Initial ThirdAndFourthCharacters
master m st
tempdb t mp
model m de
msdb m db
  • Using Substring with “Order by” Clause

Let us understand the concept of the substring in the easiest way with the help of a table Geography. Table Geography

Region_Name Store_Name
East Chicago
East New York
West Los Angeles
West San Diego

Here, we can arrange the store name as per the requirement in any order. For example –


SELECT Store_Name
FROM Geography
ORDER BY SUBSTR (Store_Name, 2, 4);

In this query, the store_id 4 is placed at the top then second, first, and third. The result set for the query is given below.

Store_Name
San Diego
New York
Chicago
Los Angeles
  • Using substring function to work with date and time

With the substring function, the input values can be truncated using the CHARINDEX function to get the data and time. And the derived string is typecast to date-time values to compare with other time-date values. Here, it is compared against the GETDATE () function. With this code, it is easy to find the initial position and covert the data type to the required format or cast functions. With the CHARINDEX function, locate the position of “/” in the string. Once you find the position, subtract the value by 3 to get the initial value for the Substring function. Similarly, the search is performed to locate the final position of “, (comma)” in the string. In this way, you can yield the date and time value for the given strings. The final output is given below.

  • Using Substring to create a simple sub-select

In the SQL server, a sub-select is the nested SELECT statement. In the SQL, the final output of a select statement is a table effectively. It usually exists in memory but can be always used as a table based on the convenience. Here, we will see how to transform columns using substring function and use it as a table for the SQL join statement.

SQL Server Training & Certification

Read: Different Types of SQL Server & SQL Database Functions
  • No cost for a Demo Class
  • Industry Expert as your Trainer
  • Available as per your schedule
  • Customer Support Available

 Look at the temp table below where the first two characters in the column represent a state and the last four characters show the state code. In the same way, when we analyze the second column, the first two characters in the column represent the country and the last four characters show the country code. With the SQL substring function, these two columns can be parsed and transformed effectively into four new columns that can be used similarly to a table in the database. The parsed table look more meaningful than the previous one as shown below.

Wrapping Up:

In this blog, we have discussed multiple examples of SQL Substring function and how it can be used to manipulate data in your database or the result set. It makes sure that the output of SQL query is formatted well as per your expectations or business requirements. Further, there are different ways to transform the data that can be used over others.

Here, we have used SUBSTRING function for your reference but it is not the single option but we can use more as per the scenario. In a few cases, data volume, database performance, and the version of SQL server define the best option to manipulate or transform the data.

I hope you enjoyed reading this blog and learned something new to use in a job environment. To learn more similar concepts on SQL server, join SQL certification course online at JanBask Training and get ready to become a database expert right away.

Read: Delete vs Truncate SQL Server – What are the Differences?


SQL Tutorial Overview

fbicons FaceBook twitterTwitter lingedinLinkedIn pinterest Pinterest emailEmail

     Logo

    JanBask Training

    A dynamic, highly professional, and a global online training course provider committed to propelling the next generation of technology learners with a whole new way of training experience.


  • fb-15
  • twitter-15
  • linkedin-15

Comments

Trending Courses

Cyber Security Course

Cyber Security

  • Introduction to cybersecurity
  • Cryptography and Secure Communication 
  • Cloud Computing Architectural Framework
  • Security Architectures and Models
Cyber Security Course

Upcoming Class

1 day 06 Oct 2024

QA Course

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing
QA Course

Upcoming Class

-0 day 05 Oct 2024

Salesforce Course

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL
Salesforce Course

Upcoming Class

6 days 11 Oct 2024

Business Analyst Course

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum
Business Analyst Course

Upcoming Class

7 days 12 Oct 2024

MS SQL Server Course

MS SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design
MS SQL Server Course

Upcoming Class

7 days 12 Oct 2024

Data Science Course

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning
Data Science Course

Upcoming Class

7 days 12 Oct 2024

DevOps Course

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing
DevOps Course

Upcoming Class

4 days 09 Oct 2024

Hadoop Course

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation
Hadoop Course

Upcoming Class

-0 day 05 Oct 2024

Python Course

Python

  • Features of Python
  • Python Editors and IDEs
  • Data types and Variables
  • Python File Operation
Python Course

Upcoming Class

14 days 19 Oct 2024

Artificial Intelligence Course

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks
Artificial Intelligence Course

Upcoming Class

7 days 12 Oct 2024

Machine Learning Course

Machine Learning

  • Introduction to Machine Learning & Python
  • Machine Learning: Supervised Learning
  • Machine Learning: Unsupervised Learning
Machine Learning Course

Upcoming Class

41 days 15 Nov 2024

 Tableau Course

Tableau

  • Introduction to Tableau Desktop
  • Data Transformation Methods
  • Configuring tableau server
  • Integration with R & Hadoop
 Tableau Course

Upcoming Class

-0 day 05 Oct 2024

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews