Want more features on Pastebin? Sign Up, it's FREE!
Guest

Untitled

By: a guest on Feb 18th, 2013  |  syntax: None  |  size: 4.28 KB  |  views: 29  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. replace([column],'[sql to find 10-digit number]'
  2.           ,TRUNC(DATE '1970-01-01' + [10-digit number]/86400))
  3.        
  4. create or replace function epoch_offset(p_value in varchar2, p_start number)
  5. return number is
  6.     l_value varchar2(4000);
  7.     offset number := 0;
  8.     prev_offset number := 0;
  9.     digit_count number := 0;
  10.     epoch_start number := 0;
  11.     pos number := p_start;
  12. begin
  13.     -- replace all digits with a single one, to make searching with instr
  14.     -- simpler
  15.     l_value := translate(p_value, '1234567890', '9999999999');
  16.  
  17.     while true loop
  18.         -- find the next digit, starting as pos; first time through, pos
  19.         -- will be the p_start we were given, then it tracks where we have
  20.         -- got to
  21.         offset := instr(l_value, '9', pos);
  22.  
  23.         if offset = 0 then
  24.             -- we didn't find a digit, check if we already had a 10-digit
  25.             -- number and have just reached the end
  26.             if digit_count = 10 then -- and pos > length(p_value) then
  27.                 -- original value ends with a timestamp; so we have a 10-digit
  28.                 -- number
  29.                 exit;
  30.             else
  31.                 -- no more digits, and last set we saw was short than 10; so
  32.                 -- l_value does not contain any 10-digit numbers (at least,
  33.                 -- after p_start)
  34.                 epoch_start := 0;
  35.                 exit;
  36.             end if;
  37.         end if;
  38.  
  39.         if prev_offset > 0 and offset != prev_offset + 1 then
  40.             -- we've found a digit, but there's a gap since the last one
  41.             if digit_count = 10 then
  42.                 -- the gap denotes the end of a 10-digit number, which is
  43.                 -- what we're looking for
  44.                 exit;
  45.             end if;
  46.  
  47.             -- we've potentially started a new 10-digit number, so reset
  48.             epoch_start := offset;
  49.             digit_count := 0;
  50.             prev_offset := 0;
  51.         else
  52.             -- we've found a sequential digit
  53.             prev_offset := offset;
  54.         end if;
  55.  
  56.         -- mark where we are
  57.         if digit_count = 0 then
  58.             -- start of a potential digit-sequence, make a note
  59.             epoch_start := offset;
  60.         end if;
  61.         digit_count := digit_count + 1;
  62.         pos := offset + 1;
  63.     end loop;
  64.  
  65.     return epoch_start;
  66. end epoch_offset;
  67. /
  68.  
  69. create or replace function epoch_replace(p_value in varchar2,
  70.     p_start in number default 1)
  71. return varchar2 as
  72.     l_pos number;
  73.     l_time number;
  74.     l_value varchar2(4000);
  75. begin
  76.     -- for this iteration, find the start of a 10-digit number, starting
  77.     -- from p_start (1 on first iteration, by default)
  78.     l_pos := epoch_offset(p_value, p_start);
  79.     if l_pos > 0 then
  80.         -- found a 10-digit number; call this recursively before modifying -
  81.         -- this means we'll replace numbers with dates working from the end,
  82.         -- so the positions don't need to be adjusted for the difference
  83.         -- between the number and date lengths
  84.         l_value := epoch_replace(p_value, l_pos + 10);
  85.         -- get the 10-digit number...
  86.         l_time := to_number(substr(l_value, l_pos, 10));
  87.         -- ... and convert it to a date, with the rest of the original value
  88.         -- around it
  89.         return substr(l_value, 1, l_pos - 1)
  90.             || to_char(trunc(DATE '1970-01-01' + l_time/86400), 'DD-mon-RR')
  91.             || substr(l_value, l_pos + 10);
  92.     else
  93.         -- didn't find a 10-digit number, so return what we started with
  94.         return p_value;
  95.     end if;
  96. end epoch_replace;
  97. /
  98.        
  99. with tmp_tab as (
  100.     select '1022089483 blah blah blah blah blah 1022094450 blah blah blah blah blah blah 1022095218 blah blah blah blah' as value from dual
  101.     union all
  102.     select 'blah 1022089483 blah 1022094450' from dual
  103.     union all
  104.     select 'blah 1022089483 98765 1022094450 1234' from dual
  105.     union all
  106.     select 'blah 1022089483 98765 1022094450 1022095218 1234 123456789 12345678901 123' from dual
  107. )
  108. select epoch_replace(value) from tmp_tab;
  109.  
  110. EPOCH_REPLACE(VALUE)
  111. ------------------------------------------------------------------------------------------------------------------------
  112. 22-may-02 blah blah blah blah blah 22-may-02 blah blah blah blah blah blah 22-may-02 blah blah blah blah
  113. blah 22-may-02 blah 22-may-02
  114. blah 22-may-02 98765 22-may-02 1234
  115. blah 22-may-02 98765 22-may-02 22-may-02 1234 123456789 12345678901 123
clone this paste RAW Paste Data