Today's Offer - SQL Server Certification Training - Enroll at Flat 10% Off.

- 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.

Read: What is SQL Server Replication and How it Works?
  • 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.

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 Curriculum 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.

Read: How to Create Table in SQL Server by SQL Query?

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.


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.

Read: MSBI Interview Questions & Answers for Fresher, Experienced
  • 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. free SQL Server demo 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: How to Use Like Operator in SQL Server?

SQL Tutorial Overview


    Janbask Training

    JanBask Training is a leading Global Online Training Provider through Live Sessions. The Live classes provide a blended approach of hands on experience along with theoretical knowledge which is driven by certified professionals.


Trending Courses

AWS

  • AWS & Fundamentals of Linux
  • Amazon Simple Storage Service
  • Elastic Compute Cloud
  • Databases Overview & Amazon Route 53

Upcoming Class

4 days 24 Nov 2019

DevOps

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

Upcoming Class

5 days 25 Nov 2019

Data Science

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

Upcoming Class

5 days 25 Nov 2019

Hadoop

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

Upcoming Class

6 days 26 Nov 2019

Salesforce

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

Upcoming Class

14 days 04 Dec 2019

Course for testing

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

Upcoming Class

34 days 24 Dec 2019

QA

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

Upcoming Class

13 days 03 Dec 2019

Business Analyst

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

Upcoming Class

5 days 25 Nov 2019

SQL Server

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

Upcoming Class

8 days 28 Nov 2019

Comments

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews