SQL Server String Functions: Manipulating Database Values in Your Programs

Just like any other programming language, SQL Server has internal functions that manipulate strings. Strings can be any alphanumeric value including special characters. You can use these functions to “scrub” data in the database or change these values after receiving them from a front-end application. There are several SQL string functions, but getting to know the most popular ones will help you figure out how to create your stored procedures.

New to SQL Server? Learn how to work with the language as a beginner at Udemy.com.

What is a SQL String?

Most programs use two basic type of input: numbers and words. Numerical input is used for calculations, but what about the input such as an address, country or first and last name? These values are strings. Every programming language has string variables and SQL Server is no different. You use these variables to store words or a collection of characters in your database.

Declaring a String Variable in SQL Server

The string data type in SQL Server is “varchar.” The following SQL Server statement declares a new string variable:

DECLARE @mystring varchar(50)

The “@” character indicates to SQL that it’s a variable. In this case, the variable name is “@mystring.” If you accidentally forget the @ character, SQL Server assumes you’re referencing a column, which usually returns an error. Therefore, make sure you use the @ character each time you reference a variable.

In the statement above, a local variable is declared. Local variables in SQL Server are similar to object-oriented front-end coding languages. SQL Server creates the variable in memory and then destroys it after the program runs. This variable is only available in the local stored procedure or program.

The next part of the statement defines the size of the string. The number in parenthesis tells the database server the size of the string to allocate. In this example, you can work with 50 characters. You can specify up to 8,000 characters in your varchar variable. You can also use the “max” keyword instead of a character count. Using “max” allows you to store up to 2^31-1 bytes in your variable.

It’s important to note that your variable string length should not exceed the column definition length in your tables. For instance, if you only allow 30 characters in your SQL Server table but your variable allows for 50, SQL Server will truncate data when you insert or edit the data. Some front-end applications don’t account for this issue and your program continues without alerting you or the user that an error has occurred. This obviously creates a data integrity issue, and it can be a critical error when you store data.

Learn how to work with SQL Server to better manage your website database.

Concat: Join Two Strings Together

Most programming languages have some kind of function that joins two strings together using a “concat” or “concatenation” function.

SQL Server has two ways you can join two or more strings: the “concat” function or the “+” character. The following code is an example of using the “concat” function:

DECLARE @mystring varchar(50)
SET @mystring = concat( ‘My name is ‘, ‘Jennifer’ )
SELECT @mystring

The first statement declares the variable. The second statement uses the “SET” keyword, which is the keyword that assigns a value to a variable. In this example, the concat function joins two strings “My name is” and “Jennifer.” However, you can add any number of strings in the function. If a string is NULL, the concat function will just add “NULL” between the strings, which basically displays a string as if nothing is there for “NULL.” Take the following code:

DECLARE @mystring varchar(50)
SET @mystring = concat( ‘My name is ‘, NULL, ‘Jennifer’ )
SELECT @mystring

The above code would display the same as the previous snippet. The result is “My name is Jennifer” for both SQL snippets.

LEN: Get a String’s Length

The “len” function gets a string length. This can be useful if you need to know the length of the string to either remove characters, delete characters or verify length before inserting into a table. The len function is generally used for logical loops or dissecting strings. The following code displays the length of a string:

DECLARE @mystring varchar(50)
SET @mystring = ‘My name is Jennifer.’
SELECT len(@mystring)

In the above code, the “My name is Jennifer” string is assigned to the same variable “@mystring” as in previous examples. The procedure then gets the length of the string and displays it to your console. Notice you can use a variable in the function. You can use variables in these functions including the concat function.

LTRIM and RTRIM: Remove Spaces from the String

SQL Server is a bit different from other programming languages that remove spaces from the left and right of the string. Most programming languages have a “trim” function that removes spaces from the left and right automatically. SQL Server only has specific left and right trim functions. You use these functions when extra spaces are accidentally entered. For instance, your user might enter “my_username  “ into an input box accidentally. You don’t want to store the extra spaces, because the user will need to enter these spaces to log in. To remove the trailing spaces, you use the “RTRIM” function.

Consider the following code example:

DECLARE @mystring varchar(50)
SET @mystring = ‘My name is Jennifer.         ’
SELECT rtrim(@mystring)

The above code returns “My name is Jennifer.” The RTRIM function removes all of the trailing spaces automatically. Now, if you have prefixed spaces, you’d need to also call the LTRIM function. You can combine them into one line of code to trim the left and the right simultaneously. The following code shows you how to streamline your code to trim both sides:

DECLARE @mystring varchar(50)
SET @mystring = ‘       My name is Jennifer.         ’
SELECT ltrim(rtrim(@mystring))

The above code returns “My name is Jennifer.”

REPLACE: Replace one SQL String with Another

The REPLACE function replaces one or more characters in an existing string. The function searches for a specified string and replaces it with another. The following code is an example of the REPLACE function:

DECLARE @mystring varchar(50)
SET @mystring = ‘My name is Jennifer.’
SELECT replace(@mystring, ‘Jennifer’, ‘Jenn’)

The above code returns “My name is Jenn.” The first function parameter is the main string you want to manipulate. The second parameter is the string you want to search for. The third parameter is what you want to replace the search string for. In this case, the function searches for “Jennifer” in the @mystring variable and replaces “Jennifer” with “Jenn.” If the REPLACE function doesn’t find the search string, nothing happens.

REPLACE is also useful when you want to remove spaces within the string. We went over the RTRIM and LTRIM functions, which remove prefixed and trailing spaces. With REPLACE, you can remove spaces in between. For instance, take the following code:

DECLARE @mystring varchar(50)
SET @mystring = ‘My name is Jennifer.’
SELECT replace(@mystring, ‘ ‘, ‘’)

The above code returns “MynameisJennifer.”

SUBSTRING: Grab a String within a String

Substring is used in other languages.  Substring retrieves a number of characters from one string and either assigns it to a new variable or just display and store the new string part. The following code demonstrates the SUBSTRING function:

DECLARE @mystring varchar(50)
SET @mystring = ‘My name is Jennifer.’
SELECT substring(@mystring, 3, 7)

The above code returns “name is.” Notice that the SUBSTRING function’s “start” location starts with an index of 0. This means that the first character is located at index 0 and the count increases from there. If you specify the length that you want to retrieve that’s longer than the string’s length, then the entire string from the start location parameter is returned.

PATINDEX: Find the Location of a String within a String

Sometimes you need to find the location of a string. Instead of returning a string, you need the integer location of where the string was found within another string. Consider the following code:

DECLARE @mystring varchar(50)
SET @mystring = ‘My name is Jennifer.’
SELECT patindex(‘%jennifer%’, @mystring)

In the above code, PATINDEX searches for “Jennifer” in the @mystring variable (notice SQL Server basic functionality is not case sensitive). The PATINDEX function returns the integer location of where this search expression is found.  In this example, “12” is returned. If the PATINDEX function cannot find the string, a “0” is returned.

RIGHT and LEFT: Get Characters from the Right or Left Side of the String

In some cases, you want to retrieve a certain amount of characters from the left or right side of the string. For instance, you might have some data that contains the user’s full first and last name with a space in between such as “Jennifer Marsh.” How do you get just the last or first name? You accomplish this using the LEFT and RIGHT functions. With this specific example, you would identify where the space is located and then take characters based on the space’s location. Consider the following code:

DECLARE @mystring varchar(50)
SET @mystring = ‘Jennifer Marsh’
SELECT RIGHT( @mystring, 5 )

In this example, the database grabs the five characters starting from the right side of the string. In other words, the result is “Marsh.”

You can do the same except from the left side using the LEFT function. The following code uses the LEFT function:

DECLARE @mystring varchar(50)
SET @mystring = ‘Jennifer Marsh’
SELECT LEFT( @mystring, 8 )

The above result is “Jennifer.”

Using the example though, you probably need to dynamically retrieve data from a string. In this case, you need to use the LEN function and the PATINDEX function. This functionality is a bit more complicated, but it’s useful in many applications. Consider the following code:

DECLARE @mystring varchar(50)
SET @mystring = ‘My name is Jennifer.’
SELECT left(@mystring, patindex(‘ ’, @mystring) - 1)

It’s only one line of code, but the business logic is more complex than the other coding samples. The business logic is “take all characters from the left side of the string up until you find the first space character.” The PATINDEX function gets the location of the first occurrence of ‘ ‘, which is character number 3. You don’t want to take the third character, which is the space character. You could trim the result, but subtracting 1 from the number of characters you return will cut out the space character from the result. The advantage to this code is that a substring is retrieved based on a dynamic location of a specific character. This means that you can pass this statement any sentence or phrase and still obtain the all characters up to the first found space character.

The above example returns “My.” If you passed “Your name is Jennifer” to the same statement, it would dynamically locate the first instance of the space character and return “Your” as a result. Creating dynamic SQL statements such as the above are pivotal for good, scalable SQL Server applications.

STR: Convert a Decimal Number to a String

To do calculations on data, it needs to be in a numeric format such as an integer, double or float. When you retrieve input from a user, it’s usually passed as a numeric value. But, you need to convert it to a string if you store it in a table as a string or append it to an existing string variable. The following code shows you how to use the STR function:

DECLARE @mystring varchar(50)
DECLARE @mynum float
SET @mystring = ‘My name is Jennifer.’
SET @mynum = 5.23
SELECT @mystring + ‘ ‘ + str(@mynum, 1, 1)

The above code appends the decimal variable “@mynum” to the end of the @mystring variable.  The first parameter is the decimal number you want to convert. The second parameter determines the number of characters to include in the string. The decimal is considered a character, so don’t forget to include the decimal. Next, the third parameter tells the STR function how many numbers from the decimal point to include. In the code above, “5.2” is returned.

There are several more string SQL functions, but these are the most commonly used. Knowing how to manipulate strings is an important part of programming for front-end and back-end applications.

Know how to manage SQL Server with a course at Udemy.com.