T-SQL Substring: Splicing a String from Another String

Transact-SQL or T-SQL is a way to run SQL statements on your database tables. SQL Server offers several string functions, which you should know to properly manipulate and work with your data. The SUBSTRING function lets you get one smaller string from a larger string. You can also incorporate the SUBSTRING function with other functions to manipulate your strings.

New to SQL Server? Learn T-SQL and database programming at Udemy.com.

What is a String Variable?

Strings are a part of any programming language. The one difference between T-SQL and other programming languages is that most other programming languages usually use the “string” primitive data type or the “String” class to define strings. With SQL Server, a string variable is defined using the “varchar” data type. The following T-SQL statement creates a SQL string that allows for 100 characters:

DECLARE @myvar varchar(100)

In the above statement, a SQL variable named “@myvar” is declared. For this variable, the string can contain up to 100 characters. If you try to store more than 100 characters in this variable, T-SQL returns an error. If you try to store more than the allocated string length in a varchar table column, SQL truncates the data. SQL returns an error when this happens, but you usually don’t detect it in your front-end application. What happens is that the truncated data goes unnoticed for a time, so you’re forced to fix it after you realize you only store half of your data.

Using the SUBSTRING Function

The SUBSTRING uses three parameters: the string you want to search, where you want to start the splicing and the number of characters you want to splice. You can use the SUBSTRING function to store a spliced string in a column, assign a new string from the previous string or to search for a string to perform some kind of business logic.

Consider the following code:

DECLARE @myvar varchar(100)
DECLARE @newvar varchar(50)
SET @myvar = ‘This is T-SQL code.’
SET @newvar = substring(@myvar, 0, 4)
SELECT @newvar

The first two T-SQL statements create two string variables. The first one allows for 100 characters. The second one allows for 50 characters.

Next, the string “This is T-SQL code” is assigned to the first @myvar string. Next, the SUBSTRING function is called. The SUBSTRING function starts at index 0 to obtain a new string. The string’s index starts at 0, but if you use any value less than 1, the function starts at the beginning of the main string, which in this case is @myvar.

The third parameter indicates how many characters the SUBSTRING function will return. If the third parameter specifies more characters than the main string is contains, the SUBSTRING function will return the entire string from the starting point to the end. In this example, only 4 characters are returned, so the resulting string is “This.”

Finally, the SELECT statement displays the result on the screen. If this code was set in a stored procedure that returns a value to a front-end application, the return value to your application is “This.”

Learn how to work with T-SQL at Udemy.com.

Using SUBSTRING to Store a Value in a Table

You can also use the SUBSTRING function to store a value in a table. Consider the following code:

DECLARE @myvar varchar(100)
DECLARE @newvar varchar(50)
SET @myvar = ‘This is T-SQL code.’
SET @newvar = substring(@myvar, 0, 4)
INSERT into mytable (substring_column) values (@newvar)

The above code is the same except for the last T-SQL statement. The last statement inserts a value into a table named “mytable.” The @newvar variable, which contains “This” is then inserted into the substring_column in the mytable table.

Finding the SUBSTRING Starting Point Dynamically

For the most part, you need to find the starting point for the second SUBSTRING parameter dynamically. For instance, you might have a full name passed to the stored procedure. Each full name has one space between the first and last name. This space character is obviously in a different position depending on the user’s full name.

Consider the following code:

DECLARE @myvar varchar(100)
DECLARE @newvar varchar(50)
SET @myvar = ‘Tom Smith’
DECLARE @spaceloc int
SET @spaceloc = PATINDEX(‘ ‘, @myvar)
SET @newvar = SUBSTRING(@myvar, @spaceloc, 5)
SELECT @newvar

In the above code snippet, you have 3 variables declared. The first two are the same as the variables in the previous snippets. The third one is used to hold the location of where the first space character is found. In this example, the name is “Tom Smith,” so the first and only space character is in the fourth character position.

A new function is introduced in the above code. The PATINDEX function returns the location of a specific string pattern. In this example, PATINDEX searches for a space character, but you can use any number of search functions. One thing to note in the above code is that it does not take into account if the search phrase searched for is not found. If the phrase isn’t found, a 0 is returned. In the above code, if a 0 is returned, then the SUBSTRING function will return the first five characters automatically. If this does not make business logic sense for your application, then you’ll need to take that into consideration when you code your stored procedure.

The @spaceloc variable stores an integer value, which in this case is 4. You then use the @spaceloc variable to indicate where you want to start the splicing. The third parameter is set to five, so five characters are returned in this procedure. The new string is then set to the @newvar variable. You can then use this variable to store new data in a table or use it to return the new value to a front-end application. In this example, the SELECT statement is used to return the last 5 characters of the string.

Other String Functions in T-SQL

T-SQL in SQL Server has a number of functions that you can use with your stored procedures. Microsoft has a list of different string functions, or you can get a rundown on some of the most popular SQL string functions at Udemy.com.

For instance, the LTRIM and RTRIM functions remove extra spaces on the left or right of a string. These are useful when you are scrubbing data from user input. For instance, users sometimes add spaces to the beginning or the end of input. You don’t want to use these spaces when you update or insert data such as a user name and password. To remove these spaces, T-SQL has the RTRIM and LTRIM functions. The following code is an example of the LTRIM function, but the RTRIM function works in the same way except it removes spaces from the right:

DECLARE @myvar varchar(100)
DECLARE @newvar varchar(50)
SET @myvar = ‘         Tom Smith’
SET @newvar = LTRIM(@myvar)
SELECT @newvar

In the above code, T-SQL returns “Tom Smith” as the result. If you had spaces on the right, you would use RTRIM.

The REPLACE function is another common string function. The REPLACE function replaces one character for another. The most common use for this function is replacing spaces with blank spaces. Consider the following code:

DECLARE @myvar varchar(100)
DECLARE @newvar varchar(50)
SET @myvar = ‘         Tom Smith’
SET @newvar = REPLACE(@myvar, ‘ ‘, ‘’)
SELECT @newvar

In the above code, the REPLACE function replaces all spaces with a blank string. In this case, the above code returns the same value as the previous code. The result is “Tom Smith.”

T-SQL is a powerful language, and it provides you with several string functions. Use these functions to manipulate and store your data.