• API
• FAQ
• Tools
• Trends
• Archive
SHARE
TWEET

# Untitled

a guest Feb 18th, 2013 31 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
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
RAW Paste Data
Top