1
CREATE FUNCTION BIT_AND (X SMALLINT, Y SMALLINT) RETURNS INTEGER
3
-- Author: Larry Menard, 11/2005
5
IF x = 1 AND y = 1 THEN
12
CREATE FUNCTION G2_BIT_AND (BITSTRING1 CHAR(32), BITSTRING2 CHAR(32)) RETURNS CHAR(32)
14
-- Author: Larry Menard, 11/2005
15
-- Bitwise AND (two streams of 32 bits)
17
DECLARE counter INTEGER;
18
DECLARE tempint INTEGER;
19
DECLARE retval CHAR(32);
22
WHILE counter <= 32 DO
23
SET tempint = BIT_AND(CAST(SUBSTR(BITSTRING1, counter, 1) AS SMALLINT),
24
CAST(SUBSTR(BITSTRING2, counter, 1) AS SMALLINT));
26
SET retval = CAST(tempint AS CHAR(1));
28
SET retval = SUBSTR(retval, 1, counter - 1) || CAST (tempint AS CHAR(1));
30
SET counter = counter + 1;
35
CREATE FUNCTION G2_LIKE (subject VARCHAR(8000), pattern VARCHAR(8000)) RETURNS INTEGER
38
-- Author: Larry Menard, 11/2005
42
-- A UDF to perform LIKE() processing unencumbered by the limitations of DB2's LIKE().
43
-- DB2's LIKE() doesn't support column values or concatenation of non-literals.
45
-- This UDF assumes the concatenation of the pattern parts is done on the invocation of
46
-- this function. Alternatively, this function would have to be able to handle varying
49
-- This UDF doesn't currently support escape character.
51
-- Wildcards supported:
53
-- '%' = 0 or more characters
57
DECLARE subject_position, pattern_position,
58
percent_position, underscore_position,
59
pattern_chunk_end, rc, character_required INTEGER;
60
DECLARE next_chunk VARCHAR(8000);
61
-- Get rid of a few oddball cases (when no subject string provided) right off the bat
62
IF LENGTH(subject) = 0 AND LENGTH(pattern) = 0 -- ('', '')
66
IF (LENGTH(subject) = 0) AND (SUBSTR(pattern, 1, 1) != '%') -- ('', 'x%')
70
-- Oddballs are out of the way, we know that a subject line exists. Let's rock.
71
SET subject_position = 1;
72
SET pattern_position = 1;
73
WHILE (pattern_position <= LENGTH(pattern)) DO
74
IF subject_position > LENGTH(subject) -- We've reached the end of the subject line,
75
AND SUBSTR(pattern, pattern_position, 1) != '%' -- and remainder of pattern starts with anything
76
THEN -- other than '%'
79
IF (SUBSTR(pattern, pattern_position, 1) = '_') OR
80
(SUBSTR(pattern, pattern_position, 1) = SUBSTR(subject, subject_position, 1))
81
THEN -- Current two characters match (including '_' wildcard)
83
ELSE -- Current two characters do not match
84
IF SUBSTR(pattern, pattern_position, 1) = '%'
85
THEN -- start processing a 'chunk'
87
-- Is there more pattern after the current '%'?
89
IF LENGTH(pattern) > pattern_position
91
-- Are there any more wildcards ('%' or '_') in the pattern after this one?
92
SET percent_position = LOCATE('%', pattern, pattern_position + 1);
93
SET underscore_position = LOCATE('_', pattern, pattern_position + 1);
95
-- There is another wildcard later in the pattern,
97
IF (percent_position > 0) OR (underscore_position > 0)
98
THEN -- Either one or both of them are non-0
99
-- get the position of the nearest wildcard
100
IF percent_position = 0 -- Percent is 0, so next wildcard must be '_'
101
THEN -- Don't include the wildcard in the chunk
102
SET pattern_chunk_end = underscore_position - 1;
104
IF underscore_position = 0 -- Underscore is 0, so next wildcard must be '%'
105
THEN -- Don't include the wildcard in the chunk
106
SET pattern_chunk_end = percent_position - 1;
107
ELSE -- Neither are 0
108
IF percent_position < underscore_position -- '%' is closer than '_'
109
THEN -- Don't include the wildcard in the chunk
110
SET pattern_chunk_end = percent_position - 1; -- '_' is closer than '%'
111
ELSE -- Don't include the wildcard
112
SET pattern_chunk_end = underscore_position - 1; -- in the chunk
118
-- There are no more wildcards in the pattern,
120
SET pattern_chunk_end = LENGTH(pattern);
122
-- If there's another wildcard immediately following this one, there isn't really a chunk
123
-- to process. If that next wildcard is a '_', we want to simply increment the pattern and
124
-- subject positions. If that next wildcard is a '%', we want to increment only the pattern
125
-- pointer. But if that next wildcard is '_' and occurs at the very end of the pattern,that
126
-- puts us out of the WHILE loop, so we can't confirm that a valid character follows in the
127
-- source.So we set a 'character_required' variable here and test it after the WHILE loop.
128
IF pattern_chunk_end = pattern_position
130
IF SUBSTR(pattern, pattern_position + 1, 1) = '_'
132
IF pattern_position + 1 = LENGTH(pattern)
134
SET character_required = 1;
139
IF SUBSTR(pattern, pattern_position + 1, 1) = '%'
141
-- Since it will be incremented at the end of the while loop, the way
142
-- to defeat the incrementing is to decrement it now
143
SET subject_position = subject_position - 1;
147
-- Do a LOCATE() of the next chunk of the pattern
148
-- up to that next wildcard, or if no wildcard, the end of the subject string.
149
-- If that LOCATE() returns 0, match obviously failed, so return false.
150
SET next_chunk = SUBSTR(pattern,
151
pattern_position + 1,
152
pattern_chunk_end - pattern_position);
153
SET rc = LOCATE(next_chunk, subject, subject_position);
158
-- set subject_position to the end of the 'next_chunk' string in the subject string
159
SET subject_position = LOCATE(next_chunk, subject, subject_position) -- (then -1)
161
- 1; -- it will be incremented at the bottom of the loop
162
SET pattern_position = pattern_chunk_end;
163
END IF; -- LOCATE() of chunk = 0
164
END IF; -- consecutive wildcards y/n
166
RETURN 1; -- no more pattern after current '%'
167
END IF; -- more characters after current wildcard
170
END IF; -- if current pattern char is '%'
171
END IF; -- current characters match
172
SET subject_position = subject_position + 1;
173
SET pattern_position = pattern_position + 1;
175
-- We've reached the end of the pattern
176
IF (subject_position - 1) < LENGTH(subject) -- there are more characters in the subject
178
IF (character_required = 1)
179
THEN -- See that really horrible case where there are two consecutive wildcards and
180
RETURN 1; -- the second wildcard is '_' and that '_' is the last character in the pattern
181
ELSE -- if there is more subject left, that's bad
187
-- Testcases for G2_LIKE
190
-- Successful 'should match' scenarios
192
-- values g2_like ('The quick brown fox jumped over the lazy dog',
193
-- 'The quick % fox jumped over the lazy dog')
194
-- values g2_like ('The quick brown fox jumped over the lazy dog', '%')
195
-- values g2_like ('F', '%')@
196
-- values g2_like ('The quick brown fox jumped over the lazy dog',
197
-- 'The quick % fox jum_ed over the lazy dog')
198
-- values g2_like ('The quick brown fox jumped over the lazy do',
199
-- 'The quick ' || '%' || ' fox jum_ed %ver the lazy dog')
200
-- values g2_like ('The quick brown fox jumped over the lazy d',
201
-- 'The quick ' || '%' || ' fox jum_ed %ver the lazy dog')
202
-- values g2_like ('The quick brown fox jumped over the lazy dog',
203
-- 'The quick % fox jum_ed %ver the lazy dog')
204
-- values g2_like ('The quick brown fox jumped over the lazy dog',
205
-- 'The quick ' || '%' || ' fox jum_ed %ver the lazy dog')
206
-- values g2_like ('The quick brown fox jumped over the lazy dog',
207
-- 'The quick ' || '%' || ' fox jum_ed %ver the lazy do%')
208
-- values g2_like ('The quick brown fox jumped over the lazy dog',
209
-- '%The quick%jumped over the lazy dog')
210
-- values g2_like ('', '')
211
-- values g2_like ('The quick brown fox jumped over the lazy dog',
212
-- 'The quick % fox jumped over the lazy dog%')
213
-- values g2_like ('The quick brown fox jumped over the lazy dog',
214
-- '%The quick%jumped over the lazy dog%')
215
-- values g2_like ('The quick brown fox jumped over the lazy dog',
216
-- 'The quick % fox jumped ___r__he lazy dog%')
217
-- values g2_like ('The quick brown fox jumped over the lazy dog', '_%_')
219
-- Successful 'should not match' scenarios
221
-- values g2_like ('The quick brown fox jumped over the lazy dog', '__')
222
-- values g2_like ('The quick brown fox jumped over the lazy dog', '')
223
-- values g2_like ('F', 'Bar')
224
-- values g2_like ('The quick brown fox jumped over the lazy dog',
225
-- '_The quick%jumped over the lazy dog')
226
-- values g2_like ('', 'Bar')
227
-- values g2_like ('The quick brown fox jumped over the lazy dog',
228
-- 'The quick%jumped over the lazy dog_')
231
CREATE FUNCTION G2_BIT_OR (INTEGER, VARCHAR(32))
232
EXTERNAL NAME 'g2_db2_jar:g2_db2_bit_or!g2_db2_bit_or'
233
RETURNS VARCHAR(32) FOR BIT DATA
234
RETURNS NULL ON NULL INPUT
238
PARAMETER STYLE DB2GENERAL