Parse , or | delimited string inside a stored procedure

exterminator · #1 (**permalink**) 03-06-06, 08:55

How can I parse a comma or pipe delimited string inside an Informix stored procedure? What are the various functions that can help me get this done? Thanks for taking a look. Best regards.

artemka · #2 (**permalink**) 03-06-06, 15:23

what are you trying to accomplish? provide the exemple if you can

you can uase the FOR loop for parsing

exterminator · #3 (**permalink**) 03-07-06, 01:21

Thanks artemka for your reply. The thing I am trying to do is - I will be getting a string that is having different names (or whatever you may consider) separated by a delimiter. This is a work-around to passing an array into a stored procedure. Now I want to get individual elements out of it.

For loop is okay, I will have to use it - but do we have
1. any informix function that gives tokens out of the strings provided a delimiter?
2. or that gives the first index of a character (that will be , or |) and then I can use SUBSTR to get the substrings.

Thanks for your response once again. Best regards.

exterminator · #4 (**permalink**) 03-07-06, 03:20

Okay, got something. Here is the function to do this - haven't tested if it works for me but yes I have the tools (functions) to make it possible now..

Code:

CREATE PROCEDURE Cut (string VARCHAR(255), delimiter CHAR(1))
  RETURNING VARCHAR(255);
  DEFINE i INTEGER;
  DEFINE loc INTEGER;
  DEFINE res VARCHAR(255);

  LET loc = FindStr(string, delimiter);
  IF loc = 0 THEN
    RETURN string;
  END IF;

  LET res = '';

  FOR i = 1 TO loc - 1
    LET res = res || string[1,1];
    LET string = string[2,255];
  END FOR;

  RETURN res;
END PROCEDURE;

But again, I have another question linked to it. If I increase the length of the argument from 255 to anything like 10000 or something. It gives an error saying maximum length has been exceeded.

What would I do to take care of strings quite longer in size? What is the datatype that I can use? Text? The input will be a big one for me.

Text data type seems to be the one right for such big input but the thing is that I am not sure if the function FindStr would work on it? Will it? I see that we can retrieve part of the Text using start and end bytes. But to find that I need to use FindStr.

What do you suggest I must do? Best regards.

Internet Services

Web Development

Digital Marketing

Consumer Tech