String manipulation a la INSTR
In our old DB we have a NAME field. It stores last name, first name, which is '/' delimited. I need to parse out the slash so I can extract just the last name, or first name, and insert them into the appropriate columns. I was hoping to find something similar to INSTR but I haven't found an equivalent function in IDS. All I've found so far is SUBSTRING and SUBSTR, but those require the starting position and the length of the substring to be extracted. Doesn't work for me because it's unpredictable. We are running IDS 9.2, Sequent Dynix/PTX.
Jeff Glenn wrote: > > In our old DB we have a NAME field. It stores last name, first name, which > is '/' delimited. I need to parse out the slash so I can extract just the > last name, or first name, and insert them into the appropriate columns. > > I was hoping to find something similar to INSTR but I haven't found an > equivalent function in IDS. All I've found so far is SUBSTRING and SUBSTR, > but those require the starting position and the length of the substring to > be extracted. Doesn't work for me because it's unpredictable. > > We are running IDS 9.2, Sequent Dynix/PTX. Can you run the load file through an AWK/sed/ or perl script and replace the slashes with column delimiters? Write an ESQL/C program (or 4GL program with a C parser) to read in the old data and parse the record before inserting? Using strtok() this should be trivial. This is not a job SQL was intended for, use the right tools for the job, Jeff, in this case it's parsers like AWK/sed/perl/lex/strtok. Art S. Kagel
Jeff Glenn wrote:
> In our old DB we have a NAME field. It stores last name, first name, which
> is '/' delimited. I need to parse out the slash so I can extract just the
> last name, or first name, and insert them into the appropriate columns.
Hie thee to:
http://www.iiug.org/software/index_ORDBMS.html
Download Split. Thence:
CREATE ROW TYPE Person_Name (
Surname VARCHAR(32) NOT NULL,
First_Name VARCHAR(32) NOT NULL,
Title CHAR(5) NOT NULL
);
CREATE FUNCTION Person_Name ( Arg1 LVARCHAR )
RETURNS Person_Name; DEFINE szSurname VARCHAR(32);
DEFINE szFirst_Name VARCHAR(32);
DEFINE szTitle CHAR(5);
DEFINE subStr LVARCHAR;
DEFINE substrCnt INTEGER;
LET substrCnt = 1;
--
-- ISplit() is the magic UDR. Read the docs that go with the BladeLet.
-- It's quite close to the Perl SPLIT{} operator.
--
FOREACH subStr IN ( ISplit ( Arg1, '\\' ) ) DO
CASE substrCnt
WHEN 1 THEN
LET szSurname = subStr;
WHEN 2 THEN
LET szFirstName = subStr;
WHEN 3 THEN
LET Title = Substr;
END CASE;
LET substrCnt = substrCnt + 1;
END FOREACH;
RETURN ROW(szSurname, szFirstName, Title)::Person_Name;
END FUNCTION;
Hope this helps!
KR
Pb
Paul Brown <paul.NOSPAM.brown@informix.com> wrote in message
news:397F760B.E59D1567@informix.com...
>
>
> Jeff Glenn wrote:
>
> > In our old DB we have a NAME field. It stores last name, first name,
which
> > is '/' delimited. I need to parse out the slash so I can extract just
the
> > last name, or first name, and insert them into the appropriate columns.
>
> Hie thee to:
>
> http://www.iiug.org/software/index_ORDBMS.html
>
> Download Split. Thence:
>
> CREATE ROW TYPE Person_Name (
> Surname VARCHAR(32) NOT NULL,
> First_Name VARCHAR(32) NOT NULL,
> Title CHAR(5) NOT NULL
> );
>
> CREATE FUNCTION Person_Name ( Arg1 LVARCHAR )
> RETURNS Person_Name;> DEFINE szSurname VARCHAR(32);
> DEFINE szFirst_Name VARCHAR(32);
> DEFINE szTitle CHAR(5);
> DEFINE subStr LVARCHAR;
> DEFINE substrCnt INTEGER;
>
> LET substrCnt = 1;
> --
> -- ISplit() is the magic UDR. Read the docs that go with the BladeLet.
> -- It's quite close to the Perl SPLIT{} operator.
> --
>
> FOREACH subStr IN ( ISplit ( Arg1, '\\' ) ) DO
> CASE substrCnt
> WHEN 1 THEN
> LET szSurname = subStr;
> WHEN 2 THEN
> LET szFirstName = subStr;
> WHEN 3 THEN
> LET Title = Substr;
> END CASE;
> LET substrCnt = substrCnt + 1;
> END FOREACH;
>
> RETURN ROW(szSurname, szFirstName, Title)::Person_Name;
> END FUNCTION;
>
> Hope this helps!
>
> KR
>
> Pb
>
>
EXACTLY what I needed! Thanks Paul and everyone from the ug.
-jg