PostgreSQL offers many data types. Strings are the most significant and most commonly used data types. Sometimes we need to search for some terms/substrings in the strings. PostgreSQL provides many approaches to find if a string contains a substring. This article comprises methods with which we can find whether a string contains a substring or not. Let’s move towards exploring these methods.

SELECT if String Contains a Substring Match in PostgreSQL

There are several ways to select if the string contains a matching sub-string. These methods are:

● Using LIKE/ILIKE Operator

● Using position() Function

● Using substring() Function

● Using Similar to regular expression

● Using POSIX regular operators

Let’s go over all these ways one by one.

Method 1: Using LIKE/ILIKE Operator

The LIKE and ILIKE operators are simple search operators. These operators are basically used to search a substring in a string or set of strings. Let's consider a table named “simplesearch” on which we will perform our matching:

The table contains different strings. We will search for some substrings in these strings. Let's see how the LIKE operator can be utilized for this purpose.

Let’s suppose we want to find a substring i.e. “ate” We will write the query for the LIKE operator as follows:

SELECT * FROM simplesearch WHERE document LIKE '%ate%';

The table simplesearch contains a column “document” where we have to search the substring. There are two wild cards that are used in this method:

● The percentage sign “%” - matches the set of characters

● The underscore sign “_” - matches a character.

The matching is done using the wildcards and the values are returned accordingly.

So the LIKE operator searches for the substring “ate” in the document list and returns those strings in which it is present.

The output for the above query is:

In a similar way, the ILIKE operator works. The only difference is ILIKE operator does case-insensitive matching. Now if we search for the substring “AtE”, we will get the same result because the operator being used is the ILIKE operator which does not care about the case. The query can be written as

SELECT * FROM simplesearch WHERE document ILIKE '%AtE%';

The query will return the following output:

This is how we can find out if the string contains the specified substring and also get those strings containing the substring.

Method 2: Using Position() Function

The position() function is also a good alternative to find if the string contains a substring or not. The basic syntax of this function is given as

SELECT POSITION(Sub_string IN String);

The function returns the number that shows the location of the substring in the string, 0 if the substring is not present, and NULL if any of the arguments is NULL.

Let’s query the position() function for our case:

SELECT * FROM simplesearch WHERE position('ate' in document) > 0;

The above query will select if the strings in the simplesearch table contain a substring “ate”. Following is the output returned by the query:

In this particular way, we can get the strings containing our specified substring.

Method 3: Using Similar to Regular Expression

The similar expression works the same as the LIKE operator. The only difference in both these is that the “similar to” expression interprets the pattern using SQL standards. This is also a good way to get the strings containing specified substrings. We can write the query as:

SELECT * FROM simplesearch WHERE document SIMILAR TO '%ate%'

We have specified the substring and we are selecting the document(the rows that contain the strings) that are similar to the substring. The output is given as

This is a simple and effective way to do this particular task.

Method 4: Using Substring() Function

The substring() function can also be used to get the string if the specified sub-string is contained in the string. The substring() function returns the strings that contain our specified substring or are similar to that substring. Following will be a query for our case:

SELECT * FROM simplesearch 
WHERE document ~~ substring(document SIMILAR '%ate%' ESCAPE '#')

The substring() function will return all the strings that contain the “ate” substring or are similar to the “ate” substring. The returned result of the substring function is matched with the document, column that contains all the strings, using the ~~ operator, and if they both match, the string is selected from the table and returned.

The output for the above query is:

So this is how we can select the string if it contains the specified substring.

Method 5: Using POSIX Expression

The POSIX expression such as Regexp_match() can be used for this purpose. The regexp_match() expression takes in the string and the substring as arguments and returns the matched substring.

We can write the following query for the POSIX expression:

SELECT regexp_match('The United   States of America', '..ate.')

This will result in the text that shows whether the string contains the substring or not. The output is given as:

So this is how we can use all the above functions to select if the string contains a substring Match in PostgreSQL.

Conclusion

We can select if the string contains our specified substring in Postgres by using the five methods that we have discussed above. The first way is by using simple search operators such as the LIKE and ILIKE operators. The second way is by using the position() function, thirdly it's the substring() function that we can use for this purpose. Another expression named “similar to” can be used and lastly, it's the POSIX expression such as regexp_match() that can be used to select the string if it contains a substring match.