Introduction to SQL Server’s Common String Functions

The built in SQL String functions make it possible for you to find and alter text values, such as VARCHAR and CHAR datatypes, in SQLServer. Using these functions, you can alter a text value such as changing “Smith, Joe” to “Joe Smith.”

If you are not familiar with SQL functions, then I would recommend starting with the Introduction to SQL Server Built-In Functions.

To get the most of this and our other lessons, be sure to practice using the examples!

All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database. You can get started using these free tools using my Guide Getting Started Using SQL Server.

Introduction to SQL Server’s String Functions

The t-SQL string functions are used to manipulate or return information about text expression such as CHAR and VARCHAR datatypes.

There are many string functions within SQL at your disposal. I would generally categorize them as:

• Position
• Transformation
• Character Set
• Soundex

All of the functions are listed on the String Functions (Transact-SQL) page. I would recommend visiting that page to learn about each function.

Rather than reiterate that material, we’ll focus on the functions I’ve seen in commonly used in business.

In the following tables, I categorized the functions and color coded them. The color code corresponds to the likelihood you would use that particular function in a business environment. Green are most likely to be used, and red less.

This isn’t a strict scale, and all functions have a use in some business case, but I wanted a way to help you winnow down the field to those most relevant.

Here is my attempt:

Functions Used to Find Position

CHARINDEX

The CHARINDEX function is used to find the position of one string within another. This can be handy when you need to break data apart by dashes or commas.

The general form for the CHARINDEX function is:

CHARRINDEX(value to find, value to search)

Where value to find is one or more characters that you wish to find in the value to search.

If the value is found, then the starting positon is returned. If the value isn’t found, then 0 (zero) is returned.

If either the value to find, or value to search are NULL, then NULL is returned.

LEN

The LEN function returns the number of characters in a string.

In the following example, you can see that the length of “SQL Server” is 10.

Length is rarely used on its own. It is used mainly in conjunction with other functions, such as LEFT and RIGHT.

Functions Used to Transform Strings

LEFT and RIGHT

The LEFT and RIGHT functions are used to return either the beginning or ending portion of a string.

LEFT will return the beginning characters; whereas, RIGHT is used to return the ending characters.

The general form for the LEFT function is:

LEFT(value, length)

Where value is the string you’re working with, and length is the number of characters you wish to return from the beginning of value.

If the length is greater than the number of characters in the value, then the entire string is returned.

IF length is negative, an error is thrown.

If either length or value is NULL, then NULL is returned.

The RIGHT string function works much like LEFT, but it returns the ending characters as opposed to the beginning.

SELECT RIGHT(‘SQL Rocks!’,6)

Returns the value ‘Rocks!’

Suppose the Production Manager wants a distinct list of all product number prefixes. How could you return that list?

If you look at the Production.Product ProductNumber column value, you’ll notice the prefix is the first two characters. So knowing what we do now about the LEFT function, the solution is a snap!

Here is what you can write:

SELECT DISTINCT LEFT(ProductNumber,2)
FROM   Production.Product

The LEFT function returns the first two characters. Since we’re using the DISTINCT operator, duplicate results are eliminated from the result.

OK, so now the production manager is getting crazy! The last character of the ProductNumber prefix means a lot to her. She wants to know what those distinct values are. What can we do to help?

We can use LEFT and RIGHT in conjunction. We already know how to get the LEFT most characters, now all we need to do is take the last character from that result and return it as shown below.

Here is the query you can use:

SELECT DISTINCT RIGHT(LEFT(ProductNumber,2),1)
FROM   Production.Product

Like other functions, you can nest string functions. Just remember that the results of one function can be used in another. You should read these expressions from the “inside out.”

SUBSTRING

The SUBSTRING function is used to return characters from within another string.

The general form of the SUBSTRING function is:

SUBSTRING(value, position, length)

Where value is the string you’re working with, position is character to start returning characters, and length is the number of characters to return.

If length is negative, an error is returned.

If either position or length are NULL, then NULL is returned.

For example:

SUBSTRING(‘SQL Server’,5,3)

returns ‘Ser’. It the sequence of three characters starting in the fifth position.

SUBSTRING is a generalized form of the LEFT and RIGHT functions.

Extra Credit – Show how LEFT and RIGHT can be used together to simulate SUBSTRING. Show your answer in the comments below.

LEFT(‘SQL Server’,4) and SUBSTRING(‘SQL Server’,1,4) both return the beginning four characters.

RIGHT is a little more complicated!

RIGHT(‘SQL Server’,4) can be written as SUBSTRING(‘SQL Server’,7,4)

Which says to start at the 7^th position and then return the next four characters. The general form for this, since you won’t always know the length of the value, is:

SUBSTRING(‘SQL Server’, LEN(‘SQL Server’),4)

Here is an example using SQL columns.

SELECT LEFT(Name,2) as Left2,
       SUBSTRING(Name, 1, 2) as Substring2,
       RIGHT(Name,3) as Right3,
       SUBSTRING(Name, LEN(Name)-2,3) as Substring3
FROM   Production.Product

Whose results are:

UPPER and LOWER

The UPPER and LOWER string functions are used to return values whose characters are in all upper or lower case respectively.

The general form for the UPPER function is:

UPPER(value)

where value is the string you’re working with.

If value is NULL, then NULL is returned.

The form and behavior for LOWER is similar.

Here is an example query:

SELECT FirstName + ' ' + LastName as FullName,
       UPPER( FirstName + ' ' + LastName) as UpperName,
       LOWER( FirstName + ' ' + LastName) as LowerName
FROM   Person.Person

Which returns:

REPLACE

The REPLACE function is good when you wish to find one or more characters in a string and replace them with other characters. A common application is to replace all dashes ‘-‘ with spaces.

Here is the general form of the REPLACE function:

REPLACE (value, pattern, replacement)

Where value is the string to work with, pattern is the portion of the string you wish to find and replace, and replacement is the value to replace the pattern.

If any of the parameters are NULL, then REPLACE returns NULL.

If pattern isn’t found, nothing is replaced, and value is returned in its original form.

In business, you’ll come across data from two separate systems, such as your systems and a customer or supplier’s systems where data is treated differently. This can cause issues, especially when it comes to matching.

Once common issue I’ve seen occurs with part numbers. Consider Adventure Works. Within the company, part numbers are formatted with dashes, such as ‘AR-5381’; however, some suppliers have replaced the dashes with spaces like so ‘AR 5381’.

Before we do a large scale data match, the production team wishes to provide the suppliers with our parts list with the dashes replaced with spaces.

Here is a query we could run to do so:

SELECT Name,
       ProductNumber,
       REPLACE(ProductNumber,'-',' ') as SupplierProductNumber
FROM   Production.Product

And the result we can provide to the suppliers is:

Though you can’t have blank pattern, and if you think about it, that wouldn’t’ make too much sense, you can replace a pattern with a blank value. Why? Well, a good reason is to strip characters out of a string.

In the ProductNumber example, there may be cases where we want to use the product number without dashes. This query could be used in that case:

SELECT Name,
       ProductNumber,
       REPLACE(ProductNumber,'-','') as SupplierProductNumber
FROM   Production.Product

Notice that two single quotes together as ” represents an empty string.

LTRIM and RTRIM

LTRIM and RTRIM are used to remove leading and trailing spaces of string.

LTRIM is used to remove spaces from the beginning of the string; whereas RTRIM removes spaces from the end.

The general form for LTRIM is:

LTRIM(value)

Where value is the string you wish to work with. If value is NULL, then NULL is returned.

For example consider

SELECT LTRIM('    Product Types')

Which returns ‘Product Types’.

If you want to remove spaces from both the beginning and end of a string, you can use both functions.

SELECT RTRIM(LTRIM('    Product Types     '))

Removes both the leading and trailing spaces.

These functions come in handy when importing data from text files, especially fixed formatted files.

The post Introduction to SQL Server’s Common String Functions appeared first on Essential SQL.