An Overview of SQL Text Functions

In this article, we will discuss the ins and outs of the most common SQL text functions, which make it possible for you to find and work on string values.

Before we dive into text functions, let’s quickly recap what an SQL function is. Simply put, a function is a predefined built-in piece of code that you can use in your SQL query. For example, the functions SUM(), AVG(), and COUNT() are applied to numerical variables to calculate the sum, average, and number of records respectively.

We’ll use the following OrderDetails table from the well-known Northwind database to demonstrate how the SUM() function works.

The objective is to return the total Quantity ordered across all 518 orders.

QUERY:

SELECT SUM(Quantity) AS Total_Ordered_Quantity
FROM OrderDetails

RESULT:

Number of Records: 1

In the above example, the SUM() function was used to calculate the total of all quantities ordered. The argument(s) (or parameter(s)) of the function is defined within the parentheses. According to SQL syntax, parentheses are needed even when no parameters are passed into the function.

Text Functions in SQL

Databases come with many different built-in functions. Therefore, it is important to know the most commonly used functions so you can write queries effectively and efficiently, without having to create your own user-defined functions from scratch. LearnSQL.com’s Standard SQL Functions course cover all common text, numerical, date and time, and aggregate functions using detailed practical examples, walkthroughs, and exercises.

In this article, we’ll be looking at some of the most widely used SQL text functions. These work on text data types like VARCHAR, CHAR, and TEXT.

Note that different databases have different built-in functions. In this article, we will only focus on the standard SQL functions, which work for most SQL-compliant databases. (Consult your database documentation for details.)  For the examples shown below, we will be using the Customers table from the same Northwind database.

CHAR_LENGTH()

The CHAR_LENGTH() function returns the number of characters in a string. This function is rarely used on its own; you’ll usually find it in conjunction with other functions.

If the input string is empty, CHAR_LENGTH() will return 0. If the input string is NULL, the function returns NULL. The syntax is quite straightforward – just put the relevant column name in the parentheses.

Let’s see an example below where we calculate the length of the string for each of the records in the CustomerName column of the Customers table.

QUERY:

QUERY:

SELECT CHAR_LENGTH(CustomerName) 
FROM Customers;

RESULT:

Number of Records: 91

Note that in the results shown above, the string length includes the spaces between words (i.e. between the first, middle, and last names).

UPPER() and LOWER()

The UPPER() and LOWER() functions convert each letter within a defined string to uppercase or lowercase characters. These text functions are often used for data cleanup or to prepare data for subsequent steps. For example, the REPLACE() function is case sensitive, so you’d need to convert any strings to the same case as the criteria text for REPLACE() to work.

The general syntax of both functions is quite simple; again, the column name goes inside the parentheses. Let’s try converting all the records in the City column to uppercase.

QUERY:

SELECT UPPER(City) as CITY 
FROM Customers;

RESULT:

Number of Records: 91

The syntax and application of LOWER() is exactly the same as UPPER().

Finally, if a given value is NULL, then the function returns a NULL.

SUBSTRING()

The SUBSTRING() function extracts a substring (i.e. part of a string) within a string. It starts from a specified location and extracts a substring of a specified length.

The general syntax of the SUBSTRING() function is:

SUBSTRING(string, start, length)

• The string.
• The position (the first position is 1) to start the extraction.
• The length (i.e. number of characters) to return.

Note that all parameters are mandatory.

If either the position or length parameter is NULL, then a NULL is returned. If the start parameter is larger than the string length (e.g. the string is “APPLE” and the start parameter is 10), the function returns a zero-length string. If the length parameter is a negative value, you’ll get an error and the query will not run. If the total of the length and start parameters is larger than the string length, the function returns the entire string.

Let’s explore an example where we extract the first 5 characters of each record in the CustomerName column:

QUERY:

SELECT SUBSTRING(CustomerName, 1, 5) AS ExtractName
FROM Customers

RESULT:

Number of Records: 91

REPLACE()

The REPLACE() function finds and replaces all occurrences of a substring (within a given string) with a new substring. Note that the search portion of the function is case sensitive.

The general syntax of the REPLACE() function is ...

REPLACE(string, old_string, new_string)

 … where string is the string (or column name) to search, old_string is the set of characters to replace, and new_string is the replacement.

Let’s see an example that will replace the abbreviation “Str” with “Street” in the Address column.

QUERY:

SELECT REPLACE(Address, 'Str.', 'Street') as Address 
FROM Customers;

RESULT:

Number of Records: 91

In the example above, we are replacing “Str.”, the old_string, with “Street”, the new_string, in the Address column. Replacements like this are often done during data cleanup to make the data more consistent for later analysis.

If the new_string parameter is NULL, then REPLACE() returns NULL as well. If the old_string parameter isn’t found, nothing is replaced; the string is returned in its original form, without replacement.

TRIM()

The TRIM() function removes leading and trailing spaces (or other characters that you specify as an optional parameter) from the start and/or end of a string. It is typically used to remove any unnecessary characters or spaces from a dataset before analysis.

The general syntax of the TRIM() function is:

TRIM([‘TrimCharacters’ FROM] string)

Here, TrimCharacters (the optional parameter) are the character(s) to remove from the string. If this is not specified, SQL removes trailing and leading spaces from the string.

In the example below, we apply the TRIM() function to the Address column. In this dataset, the entries are already quite clean. But let’s just see how the TRIM() function works if we specify that all numerical digits should be trimmed.

QUERY:

SELECT TRIM('0123456789' FROM Address) As Trimmed_Address 
From Customers

RESULT:

Number of Records: 91

We can see that all numbers have been trimmed from both the start and end of each address. Each of the characters specified in the TRIM() function within TrimCharacters are treated and trimmed individually. Additionally, once the defined characters are trimmed, the leading and trailing spaces are trimmed as well. As an example, for Hanover Square, ‘120 ’ is trimmed from the leading end, while for Avda. De la Constitución, ‘2222’ is trimmed from the trailing end.

The TRIM() function is a neat way to combine the functionalities of the LTRIM() and RTRIM() functions, which  respectively remove leading and trailing spaces. For a detailed walkthrough, refer to How to Remove Leading and/or Trailing Spaces From a String in T-SQL.

Putting SQL Text Functions to Work

I hope these examples of commonly-used SQL text functions helped you understand them. Data analysts and developers use text functions frequently, and mastering these functions is a key component of their SQL repertoire.

The best way to master SQL functions is through practice! I encourage you to learn using practical examples like the ones in our Standard SQL Functions course. If you want to specialize in the PostgreSQL and T-SQL dialects, check out our Common Functions in PostgreSQL and Common Functions in MS SQL Server courses.