Satyaki De - SQL Exploration

I'm a software engineer. I love my Oracle more than me. And, i'm senior forum member in OTN from Dec 2006 and completed my OCA. This blog mainly deals with Oracle & it's relevant technologies from useful perspective. Currently, I'm working as Teradata & Informatica ETL Solution designer. And, also take part in Teradata forum discussion.

Regular Expression on Teradata 14.0

Posted by Satyaki De
Sunday, February 23, 2014
comments (1)


I've been working for more than 8 years in Oracle 10g, 11g & worked significant queries on Regular expressions in various scenario using SQL. It is real handy if you know how to use it & can reduce lots of pain with single SQL. And, the performance will be better compared to the total effort to achieve the same functionalists by using multiple SQL queries or PL/SQL Procedures.

 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
2
3
4
5
SELECT regexp_replace('SatyakiDe','([[:lower:]]{1,})([[:upper:]]{1,})','\1 \2') AS COL_VAL;
 
COLA             COL_VAL
---------------- ----------------------------------------
SatyakiDe        Satyaki De


Case 2,

 
1
2
3
4
5
select regexp_replace('919047242526','^([[:digit:]]{2})([[:digit:]]{10})','+\1 \2') COL_VAL;
 
COLA         COL_VAL
------------ ---------------
919047255555 +91 9047255555


Case 3,

 
1
2
3
4
5
select regexp_replace('+++C','^([[:punct:]]{2})([[:punct:]]{1})(.*)$','\1\3') COL_VAL;
 
COLA COL_VAL
---- -----
+++C ++C


Case 4,

 
1
2
3
4
5
select initcap(regexp_replace(regexp_substr(' satyaki.de@mail.com','[^@]+'),'(.*)(\.)(.*)','\1 \3')) COL_VAL;
 
COLA                             COL_VAL
-------------------------------- --------------------------------------------------
satyaki.de@mail.com              Satyaki De


Case 5,

 
1
2
3
4
5
select regexp_replace('100011001','([[:digit:]]{3})([[:digit:]]{2})([[:digit:]]{4})','XXX-XX-\3') as COL_VAL;
 
COLA             COL_VAL
---------------- --------------------
100011001        XXX-XX-1001


Case 6,

 
1
2
3
4
5
select regexp_replace('123456789','([[:digit:]]{3})([[:digit:]]{3})([[:digit:]]{3})','\3.\2.\1') as COL_VAL;
 
COLA      COL_VAL
--------- ---------------
123456789 789.456.123


Case 7,

 
1
2
3
4
5
SELECT regexp_replace('satyaki9de0loves3to8work2on2sql0and2bi6tools1','[^0-9]+','',1,0,'i') AS DER_VAL;

COLA                                            DER_VAL
---------------------------------------------   ----------
satyaki1de0loves3to8work2on2sql0and2bi4tools1   1038220241



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
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
select * from dbcinfo;
 
    InfoKey  InfoData
    -------- ------------------------
1   VERSION  14.10.00.02
2   RELEASE  14.10.00.02
3   LANGUAGE SUPPORT MODE   Standard
 
 
select * from dbcinfo; 
 
    InfoKey  InfoData
    -------- ------------------------
1   VERSION  14.10.01.05
2   RELEASE  14.10.01.04
3   LANGUAGE SUPPORT MODE   Standard


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
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
select * from dbcinfo;
 
    InfoKey  InfoData
    -------- ------------------------
1   VERSION  13.00.00.15
2   RELEASE  13.00.00.15
3   LANGUAGE SUPPORT MODE   Standard
 
 
select * from dbcinfo; 
 
    InfoKey  InfoData
    -------- ------------------------
1   VERSION  13.10.07.12
2   RELEASE  13.10.07.12
3   LANGUAGE SUPPORT MODE   Standard



1
2
3
4
5
6
7
8
9
select regexp_replace('SatyakiDe','^(.*)([[:upper:]]{1,})(.*) $','\1 \2\3') AS COL_VAL;
 
select regexp_replace('SatyakiDe','^(.*)([[:upper:]]{1,})(.*) $','\1 \2\3') AS COL_VAL;
 
select regexp_replace('SatyakiDe','^(.*)([[:upper:]]{1,})(.*) $','\1 \2\3') AS COL_VAL;
                                   $
  *** Failure 3706 Syntax error:  expected something between '(' and the string 'S' keyword.
                      Statement# 1, Info =35
 *** Total elapsed time was 1 second.


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
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
SELECT * FROM dbc.dbcinfo;
 
    InfoKey  InfoData
    -------- -----------------------
1   VERSION  14.10.01.05
2   RELEASE  14.10.01.04
3   LANGUAGE SUPPORT MODE   Standard
 
 
CREATE MULTISET VOLATILE TABLE TEST_T1
  (
            COL1  VARCHAR(10)
  ) 
ON COMMIT 
PRESERVE ROWS;
 
  INSERT INTO TEST_T1 VALUES('456')
  ;INSERT INTO TEST_T1 VALUES('123x')
  ;INSERT INTO TEST_T1 VALUES('x123')
  ;INSERT INTO TEST_T1 VALUES('y')
  ;INSERT INTO TEST_T1 VALUES('+789')
  ;INSERT INTO TEST_T1 VALUES('-789')
  ;INSERT INTO TEST_T1 VALUES('159-')
  ;INSERT INTO TEST_T1 VALUES('-1-');

Lets check the data now -

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
SELECT *
FROM TEST_T1;
 
    COL1
1   123x
2   456
3   x123
4   +789
5   -789
6   y
7   159-
8   -1-


Let's look into the various scenarios now -
Case 1 (Returns Mixed Numbers, Signed Numbers & Non Numbers),

 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
SELECT *
 FROM TEST_T1
 WHERE REGEXP_SIMILAR(COL1,'^[0-9]+$','c')=0;
 
    COL1
    -----
1   123x
2   x123
3   +789
4   -789
5   y
6   159-
7   -1-



Case 2 (Returns Only Unsigned Positive Numbers),

1
2
3
4
5
6
7
SELECT *
 FROM TEST_T1
 WHERE REGEXP_SIMILAR(COL1,'^[0-9]+$','c')=1;
 
COL1
-----
456


Case 3 (Returns All Numbers including Positive, Negative & unsigned),

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
SELECT *
FROM TEST_T1
WHERE REGEXP_SIMILAR(COL1,'^[+-]?[0-9]+[+-]?$','c')=1;
 
COL1
-----
456
+789
-789
159-
-1-


Case 4 (Returns Only Non Numbers i.e. Characters),


1
2
3
4
5
6
7
SELECT *
FROM TEST_T1
WHERE REGEXP_SIMILAR(COL1,'[^0-9]+','c')=1;

COL1
----
y


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. :D

Really appreciate your time to read this post.

Regards.

Satyaki De.










Labels: , , , , , , , , , , ,

Oracle SQL & PL/SQL Basics.

Posted by Satyaki De
Sunday, August 19, 2007
comments (1)

Hi!


Friends, this page mainly deals with the basic of oracle sql & pl/sql. Here, i'm going to present many useful Oracle snippets which can be plugged into your solution. Many of the snippets which are going to be part of this blog are conceptualize and coded by me and many cases i got the idea from our brilliant otn members. I'm sure you people will like all the snippets as useful bricks. Very soon i am going to post many oracle sql & pl/sql .

Here i'm posting some useful SQL snippets which can be plugged into your environment -

SQL:

1. Dynamic Table Alteration:

Here is the sample code that demonstrate this -


scott>select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

Elapsed: 00:00:00.09

scott>
scott>create table test_dummy
2     (
3       a       varchar2(10)
4     );

Table created.

Elapsed: 00:00:05.00
scott>
scott>
scott>alter table &tab add (& col varchar2 ( 10 ));
Enter value for tab: test_dummy
Enter value for col: b
old   1: alter table &tab add (& col varchar2 ( 10 ))
new   1: alter table test_dummy add (b varchar2 ( 10 ))

Table altered.

Elapsed: 00:00:01.19

scott>
scott>desc test_dummy;
Name                 Null?    Type
-------------------- -------- --------------
A                             VARCHAR2(10)
B                             VARCHAR2(10)



2. Alternative Of Break Command:


scott>
scott>SELECT lag(null, 1, d.dname)
over (partition by e.deptno order by e.ename) as dname,
2         e.ename
3  from emp e, dept d
4  where e.deptno = d.deptno
5  ORDER BY D.dname, e.ename;

DNAME          ENAME
-------------- ----------
ACCOUNTING     CLARK
KING
MILLER
RESEARCH       ADAMS
FORD
JONES
SCOTT
SMITH
SALES          ALLEN
BLAKE
JAMES

DNAME          ENAME
-------------- ----------
MARTIN
TURNER
WARD

14 rows selected.

Elapsed: 00:00:00.52
scott>


3. Can we increase the size of a column for a View:


SQL> create or replace view v_emp
2 as
3 select ename
4 from emp
5 /
View created.

SQL> desc v_emp
Name Null? Type
----------------------------------------- -------- ----------------------------
ENAME VARCHAR2(10)
SQL>
SQL> create or replace view v_emp
2 as
3 select cast (ename as varchar2 (30)) ename
4 from emp
5 /
View created.

SQL> desc v_emp
Name Null? Type
----------------------------------------- -------- ----------------------------
ENAME VARCHAR2(30)


And here is the silly way to do this -


create or replace view temp_vv
as
select replace(ename,' ') ename
from (
select rpad(ename,100) ename
from emp
);


4. Combining two SQL Into One:


satyaki>
satyaki>select e.empno,e.deptno,d.loc "DEPT_10"
2  from emp e, dept d
3  where e.deptno = d.deptno
4  and d.deptno = 10;

EMPNO     DEPTNO DEPT_10
---------- ---------- -------------
7782         10 NEW YORK
7839         10 NEW YORK
7934         10 NEW YORK

Elapsed: 00:00:00.04
satyaki>
satyaki>select e.empno,e.deptno,d.loc "DEPT_OTH"
2  from emp e, dept d
3  where e.deptno = d.deptno
4  and e.deptno not in (10);

EMPNO     DEPTNO DEPT_OTH
---------- ---------- -------------
7369         20 DALLAS
7876         20 DALLAS
7566         20 DALLAS
7788         20 DALLAS
7902         20 DALLAS
7900         30 CHICAGO
7844         30 CHICAGO
7654         30 CHICAGO
7521         30 CHICAGO
7499         30 CHICAGO
7698         30 CHICAGO

11 rows selected.

Elapsed: 00:00:00.04
satyaki>
satyaki>
satyaki>select a.empno,(
2                   select d.loc
3                   from emp e, dept d
4                   where e.deptno = d.deptno
5                   and e.empno = a.empno
6                   and d.deptno = 10
7                 ) "DEPT_10" ,
8                 (
9                   select d.loc
10                   from emp e, dept d
11                   where e.deptno = d.deptno
12                   and e.empno = a.empno
13                   and d.deptno not in (10)
14                 ) "DEPT_OTH"
15  from emp a
16  order by a.empno;

EMPNO DEPT_10       DEPT_OTH
---------- ------------- -------------
7369               DALLAS
7499               CHICAGO
7521               CHICAGO
7566               DALLAS
7654               CHICAGO
7698               CHICAGO
7782 NEW YORK
7788               DALLAS
7839 NEW YORK
7844               CHICAGO
7876               DALLAS

EMPNO DEPT_10       DEPT_OTH
---------- ------------- -------------
7900               CHICAGO
7902               DALLAS
7934 NEW YORK

14 rows selected.

Elapsed: 00:00:00.30
satyaki>


Regards.


Satyaki De.



Labels: , , , , , , , , , , , , , , , , ,

Subscribe to: Comments (Atom)