Mar 26, 2019 
903.2k
23
NovBlack Friday Deal : Up to 40% OFF! + 2 free self-paced courses + Free Ebook - SCHEDULE CALL
- SQL Server Blogs -
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.
The basic syntax for SQL Substring is given as below –
Things to Remember: It is clear from the discussion that start controls the initial position of the substring.
The value of length controls the size of the substring.
Keep in mind that SQL function traversal is always from Left to Right.
Read: Normalization-How to Normalize Database in a World of Abnormalities
How to use SUBSTRING () and SUBSTR () in different ways with the SQL Server?
Return Data Types
| Specified expression | Return type |
| char/varchar/text | varchar |
| nchar/nvarchar/ntext | nvarchar |
| binary/varbinary/image | varbinary |
SQL Server Training & Certification
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.
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;
Here, we will check how to return the selected portion of a character string.
Read: Different Type of SQL Joins
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 |
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 |
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. 
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: How to Create Stored Procedure & Trigger in SQL Server
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 Start Your Career As MSBI Developer?
FaceBook
Twitter
LinkedIn
Pinterest
Email
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.
Cyber Security
QA
Salesforce
Business Analyst
MS SQL Server
Data Science
DevOps
Hadoop
Python
Artificial Intelligence
Machine Learning
Tableau
Search Posts
Related Posts
Receive Latest Materials and Offers on SQL Server Course
Interviews
Jun 10, 2024
903.6k
903.6k