Prashanth Jayaram

SQL Substring function overview

September 14, 2018 by

The requirement of data refactoring is very common and vital in data mining operations. In the previous article SQL string functions for Data Munging (Wrangling), you’ll learn the tips for getting started with SQL string functions, including the SQL substring function for data munging with SQL Server. As we all agree that the data stored in one form sometimes require a transformation, we’ll take a look at some common functions or tasks for changing the case of a string, converting a value into a different type, trimming a value, and replacing a particular string in a field and so on.

After reading this article, you’ll understand more about:

  1. SQL String functions
  2. Understand the SQL Server SUBSTRING function
  3. How to handle data using the SUBSTRING SQL function
  4. How to use the SQL Server SUBSTRING function in the where clause
  5. How to dynamically locate the starting and end character position in the SQL Server SUBSTRING function
  6. How to work with date-time string using the SQL Server SUBSTRING function
  7. How to Create a simple sub-select using the T-SQL SUBSTRING function
  8. And more…

In this article, we’ll deep dive into the SQL Server SUBSTRING function. SUBSTRING allows us to truncate the length of string value that is varchar data-type, when we select data from the input string or tables.

The SQL SUBSTRING function takes three arguments.

  • The first one is the field that we want to query on.
  • The second argument is the starting character,
  • and the third argument is the ending character

The SQL Server SUBSTRING function syntax is as follows:

SUBSTRING (expression, position, length)

Parameters:

  • expression: Input source string
  • position: Is an integer value that specifies the initial position from which the characters can be extracted from the given expression. The first position of an expression is always starting with 1. The initial position can be a negative integer.
  • length: Is a positive integer value. It specifies the ending limit and determines how many characters are going to be extracted from the given expression.

Note: The SQL Substring function traversal is always from left to right.

Examples

In this section, we are going to deal with some real-world scenarios using SQL string functions. For few demos, the Adventureworks2016 database is used and for some other, SQL data is generated manually. Let’s get our hands dirty and see more action.

  1. Simple data handling with the SQL Server Substring function

Let’s start with a basic SQL query. The following example returns a portion of a character string starting at an initial position 1 and extracts 5 characters from the starting position. The T-SQL SUBSTRING function is very useful when you want to make sure that the string values returned from a query will be restricted to a certain length.

In the following output, by using the SQL Server SUBSTRING function and specifying the ‘firstname’ column, the starting position of one, and the length of five characters, executing this SQL query will truncate the length of the strings that are returned from the table to five characters long. It doesn’t matter if the value itself in the table is longer than five characters.

In the following, the 3rd parameter, the length, defined as 15. This will ensure that it doesn’t matter the length of the data stored in the table column itself, the query will only return the first 15 characters. This can be helpful to make sure that the output of query data is formatted according to our expectations or what our application requires.

In the following, change the starting position as well as the length parameter, the length, defined as 10.

Extending the length to 10 characters will show longer string values. And changing the starting position to 2 will start counting characters from 2 through the string. In this case, substring function extracts 10 characters of the string starting at the second position. The SUBSTRING SQL function is very useful when you want to make sure that the string values returned from a query will be restricted to a certain length.

So you’re getting an idea of how the SQL SUBSTRING function works. The field that we want to act on, we start at which character, and we end at which character

  1. Use of the SQL Server SUBSTRING function in the where clause

In the following example, using the ‘firstname’ column, the last two characters are matched with the word ‘on’ using the SQL SUBSTRING function in the where clause.

  1. Dynamically locate the starting and end character position in the SQL SUBSTRING function

In the following example, the input string has alpha-numeric characters. Using the SQL Server Substring function, the numeric sub-set of a string from the column col is transformed using CHARINDEX. You can also use the SQL string function PATINDEX to find the initial position and length parameter of the SQL SUBSTRING function.

OR

In this example, using the SQL PATINDEX function, the initial position the string ‘-‘ is found. But the numeric value only starts in the next position so ‘1’ is added to initial position. Similarly, length is calculated by searching the next position ‘ ‘(space) and subtracting its value with the initial position gives the length. Now, we have values for all the arguments. Run the T-SQL statement.

  1. Working with DateTime strings using SQL Substring function

In the following example, you can see that the column col has a data-set and it is a datetime string. Using the SQL Server SUBSTRING function, the input values are truncated using CHARINDEX or PATINDEX function to get the date-time value. And then the derived string is type-casted to DateTime so that it can be used to compare with other DateTime values. In this case, it’s compared against the SQL GETDATE() function.

You can easily find the initial position and convert the data to required data-type (valid values) using the convert or cast functions. Using CHARINDEX, search for the position of ‘/’ of the input column. After finding the position, the value is subtracted by ‘3’ to get an initial value ‘12’ for the SQL SUBSTRING function. Similarly, the search is made to find a position for the character ’,’(comma). In this way, subtracting value with the initial position will yield the length of the string.

  1. Creating a simple sub-select using the SQL SUBSTRING function

A Sub-select, in SQL Server, is effectively a nested select statement. In SQL, the result of a select statement is effectively a table. It usually just exists in memory but it can always be used, as you would use a table. Because of this, a select statement may be used as a data source for another select statement

In the following example, you can see how the columns are transformed using the SQL Server SUBSTRING function and used as a table for the SQL join statement.

If you see the temp table values, the first two characters of the first column represent state and next four characters represents the state-code. Similarly, the second column, the first two characters represents the country and rest four characters form the country-code. Using the SQL SUBSTRING function, the two columns are effectively parsed and transformed as four new columns. These columns can be used just as if they were a table in a database. In the select statement, it’s joined with the country table so that, we can actually find the name from the country.

Note: You can also use RIGHT and LEFT string functions. You can refer to the SQL string functions for Data Munging (Wrangling) article for more information.

SQL Server SUBSTRING summary

So far, we’ve seen several examples of the SUBSTRING function in SQL Server, the character functions that SQL server makes readily available for use, and how you can use them to manipulate string values in your database and in your result set. In this way it is helpful to make sure that the output of SQL query data is formatted according to the expectations or business requirement.

We also need to understand the importance of the data-set. It is always recommended to thoroughly validate the input value. There are multiple ways to transform the data using the T-SQL SUBSTRING function. In a few cases, it is possible to transform using other SQL string functions. In some cases, volume of data, performance, and SQL Server version defines the options one over the other.

That’s all for now. I hope you enjoyed this article on SQL string functions and the SQL Server SUBSTRING function in particular. Feel free to ask any questions in the comments below.

See more

Seamlessly integrate a powerful, free SQL formatter into SSMS and/or Visual Studio with ApexSQL Refactor. ApexSQL Refactor is a SQL query formatter but it can also obfuscate SQL, refactor objects, safely rename objects and more – with nearly 200 customizable options.






Prashanth Jayaram

Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application.

My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.

View all posts by Prashanth Jayaram
Prashanth Jayaram

Latest posts by Prashanth Jayaram (see all)

String functions

About Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB. View all posts by Prashanth Jayaram

1,398 Views