...
Code Block | ||
---|---|---|
| ||
//Oracle type
CREATE OR REPLACE TYPE type_string_array AS TABLE OF VARCHAR2(2000);
//Our split function
CREATE OR REPLACE FUNCTION splitString (s_delimited_list_in VARCHAR2, s_delimiter_in VARCHAR2)
RETURN type_string_array PIPELINED
IS
l_idx PLS_INTEGER;
l_list VARCHAR2(32767) := s_delimited_list_in;
l_value VARCHAR2(32767);
ls_delimiter VARCHAR2(100) := NVL(s_delimiter_in,',');
BEGIN
LOOP
l_idx := INSTR(l_list,ls_delimiter);
IF (l_idx > 0) THEN
PIPE ROW(SUBSTR(l_list,1,l_idx-1));
l_list := SUBSTR(l_list,l_idx+LENGTH(ls_delimiter));
ELSE
PIPE ROW(l_list);
EXIT;
END IF;
END LOOP;
RETURN;
END splitString; |
As you can see, the basic idea is very simple, we just split the string based on the delimiter. Create the function in the DB using your favorite tool.
You can now use this in your SQL in the CF SQL configuration:
...
Of course, the customfield_12345 is a multiple values CF.
We don't recommend heavy usage of chocolate, since it may add to your weight, but grab one if you really need to !
Note |
---|
Note: a reader pointed to the following article: http://www.codeproject.com/Tips/584680/Using-comma-separated-value-parameter-strings-in-S. This is pretty much the same idea, but for the SQL server. |
...