Useful Oracle SQL Info

     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