SQL>
SQL> CREATE OR REPLACE FUNCTION betweenString (
2 string_in IN VARCHAR2,
3 start_in IN INTEGER,
4 end_in IN INTEGER,
5 inclusive IN BOOLEAN := TRUE
6 )
7 RETURN VARCHAR2
8 IS
9 v_start INTEGER := start_in;
10 v_numchars INTEGER := ABS (end_in) - ABS (start_in) + 1;
11 BEGIN
12 IF string_in IS NULL
13 OR NVL (start_in, 0) = 0
14 OR (start_in < 0 AND end_in > 0)
15 OR (start_in > 0 AND end_in < 0)
16 OR (start_in < 0 AND end_in > start_in)
17 OR (start_in > 0 AND end_in < start_in)
18 THEN
19 RETURN NULL;
20 ELSE
21 IF v_start < 0
22 THEN
23 v_start := end_in;
24 ELSE
25 v_start := start_in;
26 END IF;
27
28 IF NOT NVL (inclusive, FALSE )
29 THEN
30 v_start := v_start + 1;
31 v_numchars := v_numchars - 2;
32 END IF;
33
34 IF v_start > end_in OR v_numchars < 1
35 THEN
36 RETURN NULL;
37 ELSE
38 RETURN (SUBSTR (string_in, v_start, v_numchars));
39 END IF;
40 END IF;
41 END;
42 /
Function created.
SQL>