Last couple of years, I’m working on Teradata. And, on some occasion – I was expecting features like these, where I can easily manipulate data with regular expression. I’m pretty excited when I heard that Teradata also introduced Regular Expression from Version 14.0.
As a result, I tried all those features that I think can be handy & useful for various scenarios & followings are the successful queries that I get. There are two occasion, where Teradata partially able to manipulate those strings. I’ve checked the latest Teradata Manual. However, unable to find those solution. So, I’m expecting other forum members can contribute here in order to make this thread useful for every one of us. And, I’ll post here as soon as I get some answers on these partial conversions.
For better understanding, I’ve provided the actual column value & after transformation value of that column in the output. That will help us to grasp it easily – I guess. 🙂
As you can see, all the characters have filtered out from the string & only numbers are kept here. These sorts of queries are very useful in lots of different business scenarios as well.
So, any extra space may not produce desired result. And, needs to pay attention into these small details.
And, I’ve tested all these queries in the following two versions –
Hope, this will give you much more clarity. 🙂
One more thing, I would like to clarify here – my intention is to describe more features about these regexp_(similar/substr/instr/replace) functions.
I’ve received one question whether these regexp functions available in TD 13 or not in Teradata forum while posting the same article over there.
And, here is my answer to that question –
Regarding version 13,
Let us check whether they have these regexp functions or not –
Hope this will give adequate clarity to the answer of that above question.
Now, Lets see some other functionality.
REGEXP_SIMILAR has similar functionality like REGEXP_LIKE in Oracle.
Let’s see couple of such cases –
Lets prepare the table with some dummy data –
Lets check the data now –
Let’s look into the various scenarios now –
Case 1 (Returns Mixed Numbers, Signed Numbers & Non Numbers),
Case 2 (Returns Only Unsigned Positive Numbers),
Case 3 (Returns All Numbers including Positive, Negative & unsigned),
Case 4 (Returns Only Non Numbers i.e. Characters),
Hope this will give you some additional idea. 🙂
My objective is to provide basic information to my friends. So, that they can write better SQL in TD while migrating from other popular databases or new developer in TD can get a flavor of this powerful feature & exploit them in all the positive aspect & apply them properly. 😀
Really appreciate your time to read this post.