PostgreSQL仿照Oracle的instr函数

转自：http://www.myexception.cn/operating-system/480929.html

PostgreSQL模仿Oracle的instr函数

[sql]  view plain  copy

1. --  
2. -- instr functions that mimic Oracle's counterpart  
3. -- Syntax: instr(string1, string2, [n], [m]) where [] denotes optional parameters.  
4. --  
5. -- Searches string1 beginning at the nth character for the mth occurrence  
6. -- of string2.  If n is negative, search backwards.  If m is not passed,  
7. -- assume 1 (search starts at first character).  
8. --  
9.   
10. CREATE FUNCTION instr(varchar, varchar) RETURNS integer AS $$  
11. DECLARE  
12.     pos integer;  
13. BEGIN  
14.     pos:= instr($1, $2, 1);  
15.     RETURN pos;  
16. END;  
17. $$ LANGUAGE plpgsql STRICT IMMUTABLE;  
18.   
19.   
20. CREATE FUNCTION instr(string varchar, string_to_search varchar, beg_index integer)  
21. RETURNS integer AS $$  
22. DECLARE  
23.     pos integer NOT NULL DEFAULT 0;  
24.     temp_str varchar;  
25.     beg integer;  
26.     length integer;  
27.     ss_length integer;  
28. BEGIN  
29.     IF beg_index > 0 THEN  
30.         temp_str := substring(string FROM beg_index);  
31.         pos := position(string_to_search IN temp_str);  
32.   
33.         IF pos = 0 THEN  
34.             RETURN 0;  
35.         ELSE  
36.             RETURN pos + beg_index - 1;  
37.         END IF;  
38.     ELSE  
39.         ss_length := char_length(string_to_search);  
40.         length := char_length(string);  
41.         beg := length + beg_index - ss_length + 2;  
42.   
43.         WHILE beg > 0 LOOP  
44.             temp_str := substring(string FROM beg FOR ss_length);  
45.             pos := position(string_to_search IN temp_str);  
46.   
47.             IF pos > 0 THEN  
48.                 RETURN beg;  
49.             END IF;  
50.   
51.             beg := beg - 1;  
52.         END LOOP;  
53.   
54.         RETURN 0;  
55.     END IF;  
56. END;  
57. $$ LANGUAGE plpgsql STRICT IMMUTABLE;  
58.   
59.   
60. CREATE FUNCTION instr(string varchar, string_to_search varchar,  
61.                       beg_index integer, occur_index integer)  
62. RETURNS integer AS $$  
63. DECLARE  
64.     pos integer NOT NULL DEFAULT 0;  
65.     occur_number integer NOT NULL DEFAULT 0;  
66.     temp_str varchar;  
67.     beg integer;  
68.     i integer;  
69.     length integer;  
70.     ss_length integer;  
71. BEGIN  
72.     IF beg_index > 0 THEN  
73.         beg := beg_index;  
74.         temp_str := substring(string FROM beg_index);  
75.   
76.         FOR i IN 1..occur_index LOOP  
77.             pos := position(string_to_search IN temp_str);  
78.   
79.             IF i = 1 THEN  
80.                 beg := beg + pos - 1;  
81.             ELSE  
82.                 beg := beg + pos;  
83.             END IF;  
84.   
85.             temp_str := substring(string FROM beg + 1);  
86.         END LOOP;  
87.   
88.         IF pos = 0 THEN  
89.             RETURN 0;  
90.         ELSE  
91.             RETURN beg;  
92.         END IF;  
93.     ELSE  
94.         ss_length := char_length(string_to_search);  
95.         length := char_length(string);  
96.         beg := length + beg_index - ss_length + 2;  
97.   
98.         WHILE beg > 0 LOOP  
99.             temp_str := substring(string FROM beg FOR ss_length);  
100.             pos := position(string_to_search IN temp_str);  
101.   
102.             IF pos > 0 THEN  
103.                 occur_number := occur_number + 1;  
104.   
105.                 IF occur_number = occur_index THEN  
106.                     RETURN beg;  
107.                 END IF;  
108.             END IF;  
109.   
110.             beg := beg - 1;  
111.         END LOOP;  
112.   
113.         RETURN 0;  
114.     END IF;  
115. END;  
116. $$ LANGUAGE plpgsql STRICT IMMUTABLE;