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.
How to use SUBSTRING () and SUBSTR () in different ways with the SQL Server?
Return Data Types
|Specified expression||Return type|
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.
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.
Let us understand the concept of the substring in the easiest way with the help of a table Geography. Table Geography
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.
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. 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.
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.
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.
Course for testing
Receive Latest Materials and Offers on SQL Server Course