Understanding T-SQL Function SUBSTRING and Its Use Cases

by

[ad_1]

This article will explain the T-SQL string function SUBSTRING() and its use cases. SQL Server offers various string functions to meet distinct business requirements. The SUBSTRING () function is one of them which is used to fetch part of a character, binary, text, or image expression in SQL Server. The syntax used for this function is given below.

-- Syntax

SUBSTRING ( expression ,start , length )

Here, all 3 arguments are mandatory to pass to get the output. You can understand more about all 3 arguments in the below points.

  • Expression is a character, binary, text, or image expression.

    • If the specified expression is char/varchar/text, the return data type of these expressions will be varchar

    • If the specified expression is nchar/nvarchar/ntext then the return data type of these expressions will be nvarchar

    • If the specified expression is binary/varbinary/image, then the return data type of these expressions will be varbinary 

  • Start is an INT or BIGINT expression that specifies from where a query will start counting the length. The first character in the expression is 1. 

    • If you specify a start value greater than the number of specified characters, then it will return the zero expression.

    • If the start value is less than 1, the returned expression will begin at the first character that is specified in the expression

  • Length is the INT or BIGINT expression that specifies how many characters will be returned from the start value. If you will specify 1 as start value and 6 as length, then the output will be returned from character 1 to 6 but if you specify 4 as start value and 6 as length then the output will read the 4th character as the first one and will count 6 more characters from there and output will return from 4 to 10 characters including white space.

    • The query will throw an error in case you will specify the negative value to the length

    • If you specify the length as NULL, then the result set will also return as NULL

    • If the sum of length and start is more than the character present in the expression, then you will get the complete character in the result set.

Use the SUBSTRING () Function With Strings as an Expression

Here, I have given a use case of SQL function SUBSTRING with a random string specified as an expression in the below example. You can either use SQL Server management studio or SQL autocomplete to practice these use cases. The SQL complete is an advanced solution for SQL database development, management, and administration which can also be used as add-ins to the SQL Server management studio.

Now, let’s focus on our first use case. There are 5 SQL statements in the below examples with different values of Start and Length arguments to make you understand how the result set is getting returned by this function.

SELECT SUBSTRING ( ‘My name is Manvendra Deo Singh’, 1, 10 ) AS [String]
SELECT SUBSTRING ( ‘My name is Manvendra Deo Singh’, 12, 19 ) AS [Full Name]
SELECT SUBSTRING ( ‘My name is Manvendra Deo Singh’, 1, 31 ) AS [Full String]
SELECT SUBSTRING ( ‘My name is Manvendra Deo Singh’, 12, 9 ) AS [First Name]
SELECT SUBSTRING ( ‘My name is Manvendra Deo Singh’, 4, 4 ) AS [Name]

The first query has returned the first 10 characters from this string “My name is Manvendra Deo Singh” because I have specified the start value as 1 and length as 10 so the output has returned 1st character to the 10th character of this string which is “My name is”

The second query has returned the full name “Manvendra Deo Singh” because I have specified the start value as 12 and length as 19 so the output has returned from the 12th character till the 31st character which is the start + length value. The output will read 19 characters starting from the 12th character so it will become the 31st character of this string.

You can read the output of the rest of the 3 queries and understand how this function works. You can comment to us in case you have any questions on this. 

I have explained various rules for start and length arguments in the last section, now let’s test them to understand their output. I have tested the below use cases as I have explained in the last section.

  • If the length argument will be NULL, then the output will also be NULL
  • If the start argument will be negative, then the function will consider that as a starting point and calculate the specified length from them
  • If the specified length is greater than the total length of the string then the output will return a full string and not an error.
SELECT SUBSTRING (‘My name is Manvendra Deo Singh’, 1, NULL ) AS [Length is NULL],
	SUBSTRING (‘My name is Manvendra Deo Singh’, -1, 3 ) AS [Start is negative],
	SUBSTRING (‘My name is Manvendra Deo Singh’, 1, 3 ) AS [Start is positive],
	SUBSTRING (‘My name is Manvendra Deo Singh’, -1, 3 ) AS [Specified Length>fullstring]

I have executed the above statements, and we can see the output is NULL if I have specified the length argument is NULL. You can compare the next two columns of the below output where I specified negative value and positive value for the start argument, we can see the difference. When I specified the start argument value as -1 and length as 3 then it returned only one character whereas when I specified the start value as 1 then it returned 3 characters including a white space bar after “My “.

Next, we have a specified length value greater than the total length of the string expression, and we can see all string characters. I have specified length as 50 in place of 31 in the given query and we can see there is no impact. Output has returned the same as it is showing in the string expression.

If you specify the length as a negative value, then it will throw an error as shown in the below image.

SELECT SUBSTRING (‘My name is Manvendra Deo Singh’, 1, -3) AS [Length is negative]

Use SUBSTRING () Function With Table Column as an Expression

We can also use the SUBSTRING function on column names to return the output for all records saved in that column. I have a table named OrderDetails. I have used the SUBSTRING function to return the first 5 characters of each product as a tag name. You can see I have used the SUBSTRING function on column ProductName from table OrderDetails and fetched the first 5 characters of each product and displayed them in a separate column named tags in the below result set.

SELECT TOP (5) [ProductName],
[InvoiceMonth],
[Price],
[POC],
[City],
SUBSTRING (ProductName, 1, 5) AS [Tags]
FROM [TESTDB].[dbo].[OrderDetails]

You can validate the output in the below image. Look at the last column Tags which is showing the first 5 characters of their respective product which is shown in the first column named ProductName of the below image.

Now, let’s take a similar example in which I will fetch database names, their collation names, and a new column in which the SUBSTRING function will return some part of collation names. I have used system table sys.databases for this use case.

USE master
Go

SELECT name, collation_name, SUBSTRING (collation_name, 24, 10) AS [Collation values]
FROM sys.databases
WHERE database_id>4

I have excluded all system databases and executed the above query to display the result only for user databases. You can see the result based on column “collation_name” in column collation values.

Conclusion

Today, I have explained SQL function SUBSTRING and its use cases in this article. This function is used to extract some characters from a string or column expressions. We need to specify all 3 arguments that are mandatory to get the desired result. 

Please share this article and comment us your feedback in the comment section so that we can improve in a better way.

[ad_2]