Archive

Archive for July, 2009

(PLSQL) How to parse a full name into first, middle and last names?

Thursday, 30 July, 2009 Leave a comment

1st Method

CLEAR;
with t as (
select 'fabc iabc oabcd' fio from dual union all
select ' fabc        iabc oabcd' fio from dual union all
select 'fabc iabc     oabcd  ' fio from dual union ALL
select '   Baimendinov Salimjan     Danauly   ' fio
       from dual union ALL
select ' fabc     iabc     oabcd   ' fio from dual union all
select 'fabc iabc    ' fio from dual union ALL
select '  fabc   ' fio from dual union ALL
SELECT 'fabc  iabc  ' fio from dual
)
SELECT substr(fio, 1, instr(fio, ' ')) AS LastName,
       substr(fio, instr(fio, ' ') + 1,
       instr(substr(fio, instr(fio, ' ') + 1), ' ')) 
       FirstName,
       substr(substr(fio, instr(fio, ' ') + 1),
       instr(substr(fio, instr(fio, ' ') + 1), ' ') + 1,
       instr(substr(substr(fio, instr(fio, ' ') + 1),
       instr(substr(fio, instr(fio, ' ') + 1),
       ' ') + 1), ' ')) MiddleName
  FROM
  (
  SELECT
    TRIM(REPLACE(
         REPLACE(REPLACE(fio, ' ', '@!'), '!@'),
         '@!', ' '))
    || ' ' fio FROM t) t;

2nd Method

CLEAR;
with t as (
select 'fabc iabc oabcd' fio from dual union all
select ' fabc        iabc oabcd' fio from dual union all
select 'fabc iabc     oabcd  ' fio from dual union ALL
select '       fabc iabc     oabcd   ' fio from dual union ALL
select 'fabc     iabc     oabcd   ' fio from dual union ALL
select '   Baimendinov Salimjan     Danauly   ' fio
       from dual union ALL
select 'fabc iabc ' fio from dual union ALL
SELECT 'fabc  iabc' fio from dual)
SELECT
trim(substr(trim(fio),1,instr(trim(fio),' ',1,1))) 
      AS LastName,
CASE WHEN nvl(length(trim(substr(TRIM(substr(trim(fio),
         instr(trim(fio),' ',1,1),length(trim(fio)))),1,instr
         (TRIM(substr(trim(fio),instr(trim(fio),' ',1,1),
         length(trim(fio)))),' ',1,1)))),0) = 0
    THEN trim(substr(trim(fio),instr(trim(fio),' ',-1,1),
         length(trim(fio))))
    ELSE trim(substr(TRIM(substr(trim(fio),
         instr(trim(fio),' ',1,1),length(trim(fio)))),1,
         instr(TRIM(substr(trim(fio),instr(trim(fio),' ',1,1),
         length(trim(fio)))),' ',1,1))) END AS FirstName,
CASE WHEN nvl(length(trim(substr(TRIM(substr(trim(fio),
         instr(trim(fio),' ',1,1),length(trim(fio)))), 1,
         instr(TRIM(substr(trim(fio),instr(trim(fio),' ',1,1),
         length(trim(fio)))),' ',1,1)))),0) = 0
    THEN 'null'
     ELSE trim(substr(trim(fio),instr(trim(fio),' ',-1,1),
          length(trim(fio)))) END AS MiddleName FROM t;

ResultSet

LASTNAME FIRSTNAME MIDDLENAM
——– ——— ———
fabc iabc oabcd
fabc iabc oabcd
fabc iabc oabcd
fabc iabc oabcd
fabc iabc oabcd
Baimendinov Salimjan Danauly
fabc iabc null
fabc iabc null
Categories: PL/SQL Tags: , ,

(PL\SQL) How to parse a full name into first, middle and last names?

Thursday, 30 July, 2009 Leave a comment

1st Method

CLEAR;
with t as (
select 'fabc iabc oabcd' fio from dual union all
select ' fabc        iabc oabcd' fio from dual union all
select 'fabc iabc     oabcd  ' fio from dual union ALL
select '   Baimendinov Salimjan     Danauly   ' fio
       from dual union ALL
select ' fabc     iabc     oabcd   ' fio from dual union all
select 'fabc iabc    ' fio from dual union ALL
select '  fabc   ' fio from dual union ALL
SELECT 'fabc  iabc  ' fio from dual
)
SELECT substr(fio, 1, instr(fio, ' ')) AS LastName,
       substr(fio, instr(fio, ' ') + 1,
       instr(substr(fio, instr(fio, ' ') + 1), ' ')) 
       FirstName,
       substr(substr(fio, instr(fio, ' ') + 1),
       instr(substr(fio, instr(fio, ' ') + 1), ' ') + 1,
       instr(substr(substr(fio, instr(fio, ' ') + 1),
       instr(substr(fio, instr(fio, ' ') + 1),
       ' ') + 1), ' ')) MiddleName
  FROM
  (
  SELECT
    TRIM(REPLACE(
         REPLACE(REPLACE(fio, ' ', '@!'), '!@'),
         '@!', ' '))
    || ' ' fio FROM t) t;

2nd Method

CLEAR;
with t as (
select 'fabc iabc oabcd' fio from dual union all
select ' fabc        iabc oabcd' fio from dual union all
select 'fabc iabc     oabcd  ' fio from dual union ALL
select '       fabc iabc     oabcd   ' fio from dual union ALL
select 'fabc     iabc     oabcd   ' fio from dual union ALL
select '   Baimendinov Salimjan     Danauly   ' fio
       from dual union ALL
select 'fabc iabc ' fio from dual union ALL
SELECT 'fabc  iabc' fio from dual)
SELECT
trim(substr(trim(fio),1,instr(trim(fio),' ',1,1))) 
      AS LastName,
CASE WHEN nvl(length(trim(substr(TRIM(substr(trim(fio),
         instr(trim(fio),' ',1,1),length(trim(fio)))),1,instr
         (TRIM(substr(trim(fio),instr(trim(fio),' ',1,1),
         length(trim(fio)))),' ',1,1)))),0) = 0
    THEN trim(substr(trim(fio),instr(trim(fio),' ',-1,1),
         length(trim(fio))))
    ELSE trim(substr(TRIM(substr(trim(fio),
         instr(trim(fio),' ',1,1),length(trim(fio)))),1,
         instr(TRIM(substr(trim(fio),instr(trim(fio),' ',1,1),
         length(trim(fio)))),' ',1,1))) END AS FirstName,
CASE WHEN nvl(length(trim(substr(TRIM(substr(trim(fio),
         instr(trim(fio),' ',1,1),length(trim(fio)))), 1,
         instr(TRIM(substr(trim(fio),instr(trim(fio),' ',1,1),
         length(trim(fio)))),' ',1,1)))),0) = 0
    THEN 'null'
     ELSE trim(substr(trim(fio),instr(trim(fio),' ',-1,1),
          length(trim(fio)))) END AS MiddleName FROM t;

ResultSet

LASTNAME FIRSTNAME MIDDLENAM
——– ——— ———
fabc iabc oabcd
fabc iabc oabcd
fabc iabc oabcd
fabc iabc oabcd
fabc iabc oabcd
Baimendinov Salimjan Danauly
fabc iabc null
fabc iabc null
Categories: PL/SQL Tags: , ,

Till New Year

Thursday, 2 July, 2009 Leave a comment
SELECT 'Till the New Year ' ||
to_char(extract(YEAR FROM SYSDATE) + 1) || ' - ' ||
           trunc(months_between(to_date('01.01.' ||
to_char(extract(YEAR FROM SYSDATE) + 1), 'dd.mm.yyyy'), SYSDATE)) 
           || ' months ' ||
to_char(LAST_DAY(SYSDATE) - SYSDATE) || ' day ' ||
to_char(23 - to_number(substr(to_char(SYSDATE, 'hh24:mi:ss'), 1, 2))) 
           || ' hh ' ||
to_char(59 - to_number(substr(to_char(SYSDATE, 'hh24:mi:ss'), 4, 2))) 
           || ' mi ' ||
to_char(59 - to_number(substr(to_char(SYSDATE, 'hh24:mi:ss'), 7, 2))) 
           || ' ss ' || '[today:' ||
to_char(SYSDATE, 'mm:dd:yyyy hh24:mi:ss') || ']' AS "Happy New Year!!!"
FROM dual;
Categories: PL/SQL Tags: , ,

[PLSQL] Convert seconds to Day hour : min : sec

Thursday, 2 July, 2009 Leave a comment
variable vSec number;
exec :vSec := 68400;
SELECT
to_char(trunc(:vSec / 60 / 60 / 24), '09') || ' [day]' ||
to_char(trunc(MOD(:vSec / 60 / 60, 24)), '09') ||' [hh24]' ||
to_char(trunc(MOD(:vSec, 3600) / 60), '09') || ' [mi]' ||
to_char(MOD(MOD(:vSec, 3600), 60), '09') || ' [ss]'
FROM dual;
Categories: PL/SQL Tags: , ,

[PL\SQL] Convert seconds to Day hour : min : sec

Thursday, 2 July, 2009 Leave a comment
variable vSec number;
exec :vSec := 68400;
SELECT
to_char(trunc(:vSec / 60 / 60 / 24), '09') || ' [day]' ||
to_char(trunc(MOD(:vSec / 60 / 60, 24)), '09') ||' [hh24]' ||
to_char(trunc(MOD(:vSec, 3600) / 60), '09') || ' [mi]' ||
to_char(MOD(MOD(:vSec, 3600), 60), '09') || ' [ss]'
FROM dual;
Categories: PL/SQL Tags: , ,