Скачать или смотреть How to Select Specific Characters in PostgreSQL Using Regular Expressions

Learn how to select specific characters in PostgreSQL while ignoring unwanted characters using regular expressions and SQL functions.
---
This video is based on the question https://stackoverflow.com/q/63074214/ asked by the user 'SamuelFernandes' ( https://stackoverflow.com/u/12546604/ ) and on the answer https://stackoverflow.com/a/63074724/ provided by the user 'Mike Organek' ( https://stackoverflow.com/u/13808319/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: SELECT only a specific set of characters (PostgreSQL)

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Select Specific Characters in PostgreSQL Using Regular Expressions

If you're working with databases, particularly with PostgreSQL, you might encounter scenarios where you need to filter out specific characters from a dataset. This can be especially important when you have predefined character sets that you want to adhere to. In this post, we will delve into a practical example of how to achieve this using SQL.

The Problem: Filtering Characters

Let's say you have two tables as follows:

Table: tb_abcd

This table contains a list of characters along with their decimal and hexadecimal equivalents.

[[See Video to Reveal this Text or Code Snippet]]

Table: tb_users

This table holds user names.

[[See Video to Reveal this Text or Code Snippet]]

Goal

The objective is to extract only the characters from the user_name column in tb_users that are present in the tb_abcd table. If we were to apply this filtering, the expected output would be:

[[See Video to Reveal this Text or Code Snippet]]

The Solution: Using Regular Expressions

To achieve this character filtering, we can leverage PostgreSQL's powerful regular expression functions. Below is a step-by-step breakdown of the solution:

Step 1: Aggregate Allowed Characters

First, we will create a Common Table Expression (CTE) to gather all the valid characters from the tb_abcd table into a single string.

[[See Video to Reveal this Text or Code Snippet]]

string_agg(value, ''): This function concatenates all values from the value column into a single string, resulting in abcd.

Step 2: Filter User Names

Next, we will perform a selection from the tb_users table while applying the regexp_replace function to filter out any characters that are not in our aggregated string of valid characters.

[[See Video to Reveal this Text or Code Snippet]]

regexp_replace(u.user_name, '[^'||k.letters||']', '', 'gi'): This function will replace any character in user_name that is not part of the letters with an empty string (''). The upper() function then ensures that the output is in uppercase.

Final Query

Putting it all together, the complete query will look like this:

[[See Video to Reveal this Text or Code Snippet]]

Expected Result

Executing this query will yield the filtered user names:

[[See Video to Reveal this Text or Code Snippet]]

Conclusion

Using the combination of PostgreSQL's regexp_replace and string_agg, we can effectively filter characters in a database across different tables. This technique is not only useful for this small set of characters but can also be adapted to larger sets, as mentioned in the initial problem statement.

With this method, you can ensure the integrity of your datasets by selecting only the relevant characters you need. Happy querying!