Скачать или смотреть How to Effectively Use Word Boundaries in Teradata Regular Expressions

Learn how to implement word boundaries in Teradata using regular expressions to match specific strings while ignoring others.
---
This video is based on the question https://stackoverflow.com/q/66579363/ asked by the user 'VeilEclipse' ( https://stackoverflow.com/u/1657827/ ) and on the answer https://stackoverflow.com/a/66580605/ provided by the user 'dnoeth' ( https://stackoverflow.com/u/2527905/ ) 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: Word boundaries RegEx in Teradata

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 Effectively Use Word Boundaries in Teradata Regular Expressions

When working with text data in Teradata, you might find yourself needing to identify specific words or patterns while ignoring similar substrings. This scenario often arises in data cleaning, search functionalities, or when extracting specific insights. A common challenge is achieving word boundary matches similar to those afforded by popular programming languages like Python. In this guide, we’ll tackle how to replicate the functionality of word boundaries (represented as \b in RegEx) within Teradata's SQL environment.

The Problem: Matching Specific Surnames

Consider you have two strings:

String 1: "Strings are fun"

String 2: "Andrew ng is great"

You want to look for the surname ng specifically and ignore it in other contexts. In Python, you can easily achieve this with the re.findall() method using word boundaries:

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

Here, the first string returns an empty list because ng doesn’t match as a whole word, while the second returns ng as it does meet the word boundary condition.

Duplicating this behavior in Teradata can be a bit tricky, so let's explore how to do this using Teradata's Regular Expression functions.

The Solution: Using Regular Expressions in Teradata

Teradata supports PCRE (Perl Compatible Regular Expressions), which includes the \b word boundary. However, the REGEXP_SIMILAR function in Teradata requires a full match by default. To work around this, you need to introduce .* to the pattern to allow for flexible matches.

Step-by-Step Implementation

Find Surname in a String using REGEXP_SIMILAR

To check if ng exists as a complete word within a string, structure your SQL query like this:

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

Check Against the First String

Using the same logic, you can check your other string:

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

Extract the Word using REGEXP_SUBSTR

If you need to extract the specific word instead of just matching, use the REGEXP_SUBSTR function:

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

For the first string, it will return NULL because ng is not there:

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

Example SQL Queries

Here are both example queries combined for clarity:

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

Conclusion

By understanding how Teradata's regular expressions work, particularly how to leverage word boundaries with .*, you can effectively manipulate and analyze your text data. This technique enables you to precisely match and extract specific terms while ignoring unwanted noise in your data, providing cleaner and more meaningful results.

If you find yourself needing to perform similar operations, remember these fundamentals of using REGEXP_SIMILAR and REGEXP_SUBSTR effectively!