Regexp_replace of Oracle SQL and two-dimensional matrix simulation of SQL statements

Share two SQL statements

1. regexp_replace supported by Oracle SQL

SQL> select regexp_replace ('fuck', '(.) (.)', '/123') from dual;
 
Regexp_replace ('fuck ','(.)(
------------------------------
Ufkcy UO

(Haha)

There are two letters in a pair.

//////////////////////////////////////// /////////////////////////////////////

SQL> select regexp_replace ('fuck', '(.) (.)', '/123') from dual;
 
Regexp_replace ('fuck ','(.)(
------------------------------
Ukyu

Removes all characters at the odd position, leaving only the characters at the even position. spaces are characters at the odd position.

 

2. Use SQL statements to simulate a two-dimensional matrix

Idea: first, use cross join to construct a full orders table, then sort and group

Used table: EMP table, which has at least three fields: deptno, job, Sal

Deptno: the job constitutes the joint primary key. The Sal value is determined by the deptno and job fields. The Sal value may be null. Requirement: When the Sal value is empty, 0 is displayed.

 

select. deptno, B. job,
(
case
when exists (select * from EMP where deptno =. deptno and job = B. job) Then
(
select sum (SAL)
from EMP
where deptno =. deptno and job = B. job
group by deptno, job
)
else 0
end
) sum_sal
from
(
select distinct deptno
from EMP
) A
cross join
(
select distinct job
from EMP
) B
group by. deptno, B. job
order by. deptno, B. job