PostgreSQL Substring and Other SQL String Functions

PostgreSQL SubstringIf you’re a programmer, you’re probably used to the substring function available in several languages. Substring functions let you extract one string from another string. It’s a great way to trim your string down to a specific value or identify if one string is a part of the current string. PostgreSQL also has a substring function, but the syntax is different than what you’re probably used to. It’s not difficult to learn, but it’s unique from other languages you might have seen.

Get started with PostgreSQL syntax and design

Working with Strings: The Basics

You will run into strings no matter what language you prefer. With integers and decimal numbers, you can only store numeric values, so you need a way to store words and characters. String variables hold any character on your keyboard. Actually, string variables can also hold integer values, but they are stored as strings and not numbers. This means you can’t do calculations on a string value with numbers.

When you pass values to your database, you need a way to store those strings. PostgreSQL is just like any other database language. You need to define a variable first, and then you can assign a value to that variable.  The following code lets you create a variable in PostgreSQL:

customer_name VARCHAR(50);

The SQL type for strings is “varchar.” The “50” tells the database how many characters the variable can hold.  In this example, the variable named “customer_name” can contain a 50 character string.

The next step is to assign your variable a value. This can be a value passed from your program or a value you statically assign in your PostgreSQL function. The following code assigns a value to your new variable:

SET customer_name = ‘my name’;

Notice the PostgreSQL language uses the tick character to enclose a string and not the quote character like other languages.

Now that you have your string set up, you can use the substring character. The substring character is if you want to find a specific string from another. For example, you have a string with the characters “my name” in the variable. If you want to find the first two characters, you use the substring function. The following code shows you how to use the substring function:

substringVariable varchar(50)

SET substringVariable = substring(customer_name from 1 for 2);

The example above first defines a new string named “substringVariable.” This variable is used to hold the substring value after you perform the function. The second statement performs the substring and sets the new value to the secondary variable. Notice the “1 to 2” parameter. This parameter says to take the first character and take all characters up until the second character. In this example, only two characters are retrieved. However, if you changed this value to “1 to 5,” the substring function will return characters from one to five. Incidentally, most languages start the string index at zero, but this is not the case with the PostgreSQL language. The number one represents the first character.

Learn the SQL language from the ground up.

Using Substring with PostgreSQL Queries

You can also use the substring function with your PostgreSQL queries. This means that when the query returns data back to your program or to your screen, you seen the substring value in your data set. This is beneficial if you have a column with strings that are a part of two concatenated strings. For instance, if your product IDs are combined with a product name and a number, you could use the substring value to return only the name part of the string.

For instance, the following PostgreSQL query returns only the first four characters of a product identification number located in a products table:

SELECT SUBSTRING(product_name from 1 for 4), product_name FROM cities ORDER BY product_name;

In the example above, the “product_name” column is used in the substring, and the first four characters are returned. In addition to the substring, the full product name is also returned. The results are also returned in alphabetical order by the product name.

Get to know good database design to build your own data servers

You can also use the substring function and concatenate two separate values you retrieve from other strings and combine them into one. The following query concatenates two substring values into one:

SELECT SUBSTRING(product_name from 1 for 4) + SUBSTRING(product_name from 1 for 1), product_name FROM cities ORDER BY product_name;

In this example, the returned value is the first four characters of the product name and then the first character is appended to the end of the returned value. Again, the full product name is also returned and the values are sorted alphabetically in order of the product name.

The substring function is just one string function of several others when working with databases and the values contained within your tables. String functions can be difficult to manage, because there are so many ways to work with strings and you must be able to manipulate them to return the values you need for your users.

Using substring in your PostgreSQL queries also perform the string manipulation on your server, which is typically faster than doing it on your web server. You can use the substring function on your database or web server, but creating clean data sets makes it easier when you want to display data to your users.

Learn how to work with databases and design as a beginner