1.Trim Example
SELECT TRIM (' 7'),
TRIM (' 777 77 '),
TRIM (' ' FROM ' 7 7 '),
TRIM (LEADING '0' FROM '000123'),
TRIM (TRAILING '1' FROM 'Tech1'),
TRIM (BOTH '1' FROM '123Tech111')
FROM DUAL
2. Sql to add seconds, minutes, hours to
current date
SELECT SYSDATE,
SYSDATE + (1 / (24 * 60 * 60)) "1Sec Addition",
sysdate + interval '1' second,
SYSDATE + (1 / (24 * 60)) "1Min Addition",
sysdate + interval '1' minute,
SYSDATE + (1 / 24) "1HR Addition",
sysdate + interval '1' hour
FROM DUAL
1 3.Converting string into rows
with t as (select 'abcd,123,defoifcd,87765' as str from dual)
select level as n, regexp_substr(str,'[^,]+',1,level) as val
from t
connect by regexp_substr(str,'[^,]+',1,level) is not null
4.Converting string into columns
with t as (select 'abcd,123,defoifcd,87765' as str from dual)
select max(decode(level,1,regexp_substr(str,'[^,]+',1,level))) as val1
,max(decode(level,2,regexp_substr(str,'[^,]+',1,level))) as val2
,max(decode(level,3,regexp_substr(str,'[^,]+',1,level))) as val3
,max(decode(level,4,regexp_substr(str,'[^,]+',1,level))) as val4
,max(decode(level,5,regexp_substr(str,'[^,]+',1,level))) as val5
from t
connect by regexp_substr(str,'[^,]+',1,level) is not null
5.Listagg Test
create table dept_test
(
dno
number,
dname
varchar2(100 byte),
location varchar2(100 byte)
)
set define off;
insert into dept_test (dno, dname, location) values (1, 'Accounts', 'NY');
insert into dept_test (dno, dname, location) values (2, 'Delivery', 'NJ');
insert into dept_test (dno, dname, location) values (3, 'Customer Support', 'NJ');
insert into dept_test (dno, dname, location) values (4, 'Billing', 'NY');
insert into dept_test (dno, dname, location) values (5, 'Payments', 'NY');
commit;
select * from dept_test
select location, listagg (dname,',') within group (order by dname) dept_list
from dept_test
group by location
6. Notepad++ add quotes at begin & end of each line in a file
7.Convert String with comma separated values into Columns Using SQL
1. select * from test
select c1,
trim(regexp_substr(c2,'[^,]+',1,level)) c2
from test
connect by regexp_substr(c2,'[^,]+',1,level) is not null
and c2 = prior c2
and c1 = prior c1
and prior sys_guid() is not null
order by c1
