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. 🙂
Case 1,
1 |
SELECT regexp_replace('SatyakiDe','([[:lower:]]{1,})([[:upper:]]{1,})','\1 \2') AS COL_VAL; |
Case 2,
1 |
select regexp_replace('919047242526','^([[:digit:]]{2})([[:digit:]]{10})','+\1 \2') COL_VAL; |
Case 3,
1 |
select regexp_replace('+++C','^([[:punct:]]{2})([[:punct:]]{1})(.*)$','\1\3') COL_VAL; |
Case 4,
1 |
select initcap(regexp_replace(regexp_substr(' satyaki.de@mail.com','[^@]+'),'(.*)(\.)(.*)','\1 \3')) COL_VAL; |
Case 5,
1 |
select regexp_replace('100011001','([[:digit:]]{3})([[:digit:]]{2})([[:digit:]]{4})','XXX-XX-\3') as COL_VAL; |
Case 6,
1 |
select regexp_replace('123456789','([[:digit:]]{3})([[:digit:]]{3})([[:digit:]]{3})','\3.\2.\1') as COL_VAL; |
Case 7,
1 |
SELECT regexp_replace('satyaki9de0loves3to8work2on2sql0and2bi6tools1','[^0-9]+','',1,0,'i') AS DER_VAL; |
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 –
1 |
select * from dbcinfo; |
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 –
1 |
select * from dbcinfo; |
1 |
select regexp_replace('SatyakiDe','^(.*)([[:upper:]]{1,})(.*) $','\1 \2\3') AS COL_VAL; |
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 –
1 |
SELECT * FROM dbc.dbcinfo; |
Lets check the data now –
1 |
SELECT * |
Let’s look into the various scenarios now –
Case 1 (Returns Mixed Numbers, Signed Numbers & Non Numbers),
1 |
SELECT * |
Case 2 (Returns Only Unsigned Positive Numbers),
1 |
SELECT * |
Case 3 (Returns All Numbers including Positive, Negative & unsigned),
1 |
SELECT * |
Case 4 (Returns Only Non Numbers i.e. Characters),
1 |
SELECT * |
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.
Regards.
Satyaki De.
Dear Satyakide,
I am newbie to Teradata could you please help me writing the Teradata Query for the below senario’s : Appreciate your time and patience.
1. For each source first, middle, last, title, and full name field, replace all occurrences of a period or a comma with a space.
2. For each source first, middle, last, title, and full name field, parse the field into individual words using one or more spaces as a delimiter.
3. For each word in each parsed source field, convert all but the first character to lowercase.
4. For each word in each parsed source field, if the word is a single alphabetic character then add a period to the end of the word.
5. For each word in each parsed source field, capitalize the next letter following any occurrence of a dash ‘-‘ or a slash ‘/’.
6. For each word in each parsed source field, if the second character is an apostrophe and the third character is not a space and is alphabetic, capitalize the third character. Using this rule, “O’connell” would become “O’Connell”.
7. For each word in each parsed source field, if the word begins with either “Mc” or “Mac” and has at least 2 non-space characters after those values, then capitalize the next letter in the word. Using these rules, “Macconnelly” would become “MacConnelly”, but “Mack” would remain as “Mack”.
P.S : : Here first, middle, last, title pulling from BASE_TAB.PRACTN table , and full name is pulling from _tab.PROV table
Sachen, whenever you post your problem. Always, post with some sample input data & required output data in a formatted manner. So, that anyone can understand the problem. Also, you should always mention your TD version details.
Now, I’m addressing 2nd part of your post. Always, post the relevant query that you stuck. It is always better to give it a first shot by yourself. provide your query that you have tried in order to achieve the same. That will give us an idea about your way of delivering solution. Based on that, we can provide the solution that you understand better.
Remember one thing. If you easily get anything – you’ll tend to forget quickly. 🙂 But, if you spent lots of time on it – you’ll never forget the same. 😀
Just give it a try & let me know. I’ll be happy to provide couple of solution from this queries.
Thanks for your time to read my article. I hope that will give some basic idea about regular expression in TD 14.0.
Yuyudhana, listen, your article has helped me immensely. For years I’ve needed regular expressions, and your essay and examples pried open the door.
Kudos also to Teradata for implementing them, and for also doing the EditDistance() function.
Thanks Kevin. The purpose this blog is to share my knowledge and technical snippet to others. So, everyone can take benefits from it. Also, this is completely new area in TD. So, I’m encouraging many TD Developers to go with this rather making lengthy, complex and multiple SQLs to achieve the same.
Hello Satyaki,
Case 1 (Returns Mixed Numbers, Signed Numbers & Non Numbers),
:Does this work only for varchar type.Whaf it the datatype is char?
Could find the this pattern matching solution only in your site and forum.Thanks for such a page with example.
Regards
Koyel
Hi Koel,
Ideally, it should work for both varchar & char. Though, I haven’t tested that yet specifically. Why can’t you test that & let me know?
Regards.
Its throwing error while executing the same above query Error :
Column datatype does not match a Defined Type name
Which query you are talking about?
Please mention the query over here. Also, post your Teradata Version details. Did you check what version you are trying?
Hi SatrakiDe,
I’m using TD version 14, however, I’m receiving an error while executing the REGEXP_similar function.
Error: 5589. REGEXP_SIMILAR function not found.
This is my query.
select * from shristi WHERE REGEXP_SIMILAR(Val, ‘A’ )=1 ;
Could you please throw some light on this.