Function to accept string parameter and return substrings usable outside of the function

Question

I want to write a function that accepts an input string, uses REGEXP_SUBSTR to parse that string into up to five substrings, and returns the substrings to the procedure that called the function.

CREATE OR REPLACE FUNCTION PARSER_FUNCTION
(inputString IN VARCHAR2)
RETURN VARCHAR2
AS
subStrings VARCHAR2(100);
CURSOR C1 IS
SELECT REGEXP_SUBSTR(inputString, '[[:alpha:]]+', 1, 1)
     , REGEXP_SUBSTR(inputString, '[[:alpha:]]+', 1, 2)
     , REGEXP_SUBSTR(inputString, '[[:alpha:]]+', 1, 3)
     , REGEXP_SUBSTR(inputString, '[[:alpha:]]+', 1, 4)
     , REGEXP_SUBSTR(inputString, '[[:alpha:]]+', 1, 5)
  FROM DUAL;
BEGIN
OPEN C1;
/* Not sure what to do here... */
RETURN subStrings;
END;
END PARSER_FUNCTION;

In the WHERE clause of the stored procedure that calls this function, I want to be able to compare a column to each of the five substrings, something like:

WHERE table_column LIKE '%' || PARSER_FUNCTION[1] || '%'
  AND table_column LIKE '%' || PARSER_FUNCTION[2] || '%'
  AND table_column LIKE '%' || PARSER_FUNCTION[3] || '%'
  AND table_column LIKE '%' || PARSER_FUNCTION[4] || '%'
  AND table_column LIKE '%' || PARSER_FUNCTION[5] || '%'

How can I accomplish this?

MT0 · Accepted Answer · 2017-02-03T01:50:22.943

CREATE OR REPLACE FUNCTION PARSER_FUNCTION(
  inputString IN VARCHAR2,
  index       IN NUMBER
)
RETURN VARCHAR2 DETERMINISTIC
AS
  RETURN REGEXP_SUBSTR( inputString, '[[:alpha:]]+', 1, index );
END PARSER_FUNCTION;
/

Or, without regular expressions:

CREATE OR REPLACE FUNCTION parser_function(
  list      IN VARCHAR2,
  position  IN NUMBER,
  delimiter IN VARCHAR2 DEFAULT ','
)
RETURN VARCHAR2 DETERMINISTIC
IS
  p_start NUMBER := 1;
  p_end   NUMBER;
BEGIN
  IF list IS NULL OR position < 1 THEN
    RETURN NULL;
  END IF;
  IF position > 1 THEN
    p_start := INSTR( list, delimiter, 1, position - 1 ) + 1;
    IF p_start = 1 THEN
      RETURN NULL;
    END IF;
  END IF;
  p_end := INSTR( list, delimiter, 1, position );
  IF p_end = 0 THEN
    p_end := LENGTH( list ) + 1;
  END IF;
  RETURN SUBSTR( list, p_start, p_end - p_start );
END;
/

Then you can just do:

WHERE table_column LIKE '%' || PARSER_FUNCTION( 'list,list2,list3', 1 ) || '%'
  AND table_column LIKE '%' || PARSER_FUNCTION( 'list,list2,list3', 2 ) || '%'
  AND table_column LIKE '%' || PARSER_FUNCTION( 'list,list2,list3', 3 ) || '%'
  AND table_column LIKE '%' || PARSER_FUNCTION( 'list,list2,list3', 4 ) || '%'
  AND table_column LIKE '%' || PARSER_FUNCTION( 'list,list2,list3', 5 ) || '%'

(Note: This will work for when you are using AND in the where clause but might not when you are using OR as the 4th and 5th entries in the list don't exist so you will get a clause AND table_column LIKE '%%' which will always be true so you could need a bit more defensive coding to check if the return from the PARSER_FUNCTION is not NULL.)

Or you could just get rid of the function:

WHERE table_column LIKE '%' || REGEXP_SUBSTR( 'list,list2,list3', '[[:alpha:]]+', 1, 1 ) || '%'
  AND table_column LIKE '%' || REGEXP_SUBSTR( 'list,list2,list3', '[[:alpha:]]+', 1, 2 ) || '%'
  AND table_column LIKE '%' || REGEXP_SUBSTR( 'list,list2,list3', '[[:alpha:]]+', 1, 3 ) || '%'
  AND table_column LIKE '%' || REGEXP_SUBSTR( 'list,list2,list3', '[[:alpha:]]+', 1, 4 ) || '%'
  AND table_column LIKE '%' || REGEXP_SUBSTR( 'list,list2,list3', '[[:alpha:]]+', 1, 5 ) || '%'

Update:

You could also convert the list to a collection and join that to your query:

CREATE OR REPLACE FUNCTION split_String(
  i_str    IN  VARCHAR2,
  i_delim  IN  VARCHAR2 DEFAULT ','
) RETURN SYS.ODCIVARCHAR2LIST DETERMINISTIC
AS
  p_result       SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();
  p_start        NUMBER(5) := 1;
  p_end          NUMBER(5);
  c_len CONSTANT NUMBER(5) := LENGTH( i_str );
  c_ld  CONSTANT NUMBER(5) := LENGTH( i_delim );
BEGIN
  IF c_len > 0 THEN
    p_end := INSTR( i_str, i_delim, p_start );
    WHILE p_end > 0 LOOP
      p_result.EXTEND;
      p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, p_end - p_start );
      p_start := p_end + c_ld;
      p_end := INSTR( i_str, i_delim, p_start );
    END LOOP;
    IF p_start <= c_len + 1 THEN
      p_result.EXTEND;
      p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, c_len - p_start + 1 );
    END IF;
  END IF;
  RETURN p_result;
END;
/

Then you can just do:

SELECT *
FROM   your_table t
WHERE  NOT EXISTS( SELECT 1
                   FROM   TABLE( split_String( 'list1,list2,list3' ) l
                   WHERE  t.table_column NOT LIKE '%' || l.COLUMN_VALUE || '%' )

This means your list can contain any number of elements and it will check them all without having to repeatedly call extract the list item using regular expressions.

I needed to call the function from four different stored procedures. I have conditioned myself to think that any time I need to use a process more than once that I need to modularize the process. After taking another look at my issue, I think getting rid of the function was the right call. Thank you very much. — Jake, Feb 02 '17 at 18:02
Your regex does not work with a NULL list element but will return the wrong value. Try this where the 3rd element of "listC" is expected and the 2nd element is NULL: `with tbl(col1) as ( select 'listA,,listC,listD' from dual ) select REGEXP_SUBSTR( col1, '[[:alpha:]]+', 1, 3 ) from tbl;` — Gary_W, Feb 02 '17 at 22:38
So would you rather fix the regex once, in your function or numerous times, all over your code? Stick with your original instinct! — Gary_W, Feb 02 '17 at 23:20
@Gary_W Updated to not use a regular expression and to not require hard-coding the number of list items. — MT0, Feb 03 '17 at 01:33

score 0 · Answer 2 · edited May 23 '17 at 11:53

0

Consider this function:

FUNCTION  GET_LIST_ELEMENT(string_in VARCHAR2, element_in NUMBER, delimiter_in VARCHAR2 DEFAULT ',') RETURN VARCHAR2 IS
    BEGIN
      RETURN REGEXP_SUBSTR(string_in, '(.*?)(\'||delimiter_in||'|$)', 1, element_in, NULL, 1);
  END GET_LIST_ELEMENT;

As found in this post:

https://stackoverflow.com/a/25652018/2543416

Please see that thread for a discussion that may provide some info for you.

edited May 23 '17 at 11:53

Community

1
1

answered Feb 02 '17 at 15:57

Gary_W

9,933
1
22
40

For sub-strings this regular expression is fine but don't use it to count the number of elements: `SELECT REGEXP_COUNT( '1,2,3', '(.*?)(\,|$)' ) FROM DUAL;` It will count one too many but not always: `SELECT REGEXP_COUNT( '1,2,3,', '(.*?)(\,|$)' ) FROM DUAL;` – MT0 Feb 03 '17 at 01:57
@MT0 Great observation! For counting elements I usually use `REGEXP_COUNT('1,2', ',') + 1` (count the delimiters and add 1). – Gary_W Feb 03 '17 at 13:55

Function to accept string parameter and return substrings usable outside of the function

2 Answers2