Re: SQL Question (three parts of a name)
The problem is that Informix's SPL doesn't support variable array
indexing. Using a similar tactic to the upshift SPL that has been posted
often, you could try something like the following:
create procedure names3(str char(255))
returning varchar(255),varchar(255),varchar(255);define first varchar(255);
define middle varchar(255);
define last varchar(255);
define wordnr smallint;
define i,len smallint;
let wordnr = 1;
let first = '';
let middle = '';
let last = '';
let len = length(str);
for i = 1 to len
if str[1,1] = ' ' then
if wordnr > 1 then
let middle = middle || ' ' || last;
let last = ' ';
end if
let wordnr = wordnr + 1;
elif wordnr = 1 then let first = first || str[1,1];
else let last = last || str[1,1];
end if
let str = str[2,255];
end for
return first,middle,last;
end procedure;
This is supposed to return the first word of a string, the last word if
there are more words than one, and the rest of the string in the middle
if there are more than two words. I haven't tested this so I'm sure it
will need some tweaking to work properly. The basic idea is the main
thing.
Best wishes,
----------------------------------------------------------------------
John H. Frantz Power-4gl: Extending Informix-4gl
frantz@centrum.is http://www.rl.is/~john/pow4gl.html
In article <5u4sjk$pg9@cssun.mathcs.emory.edu>,
ontop@worldnet.att.net wrote:
>
> Hi Nicholas,
>
> I had the same problem about a year ago when we converted our own
> system. We had our names entered much the same way. We searched for an
> SQL alternative in vain. Perhaps there is an SPL method, but we didn't
> find it. I wrote an ESQL/C program that takes the data after it is
> unloaded from the old table and splits it into the requisite parts. I
> call it split_name.
>
> Hawk's Talon wrote:
>
> } Reply-To: nikel@bellsouth.net
> }
> } I have the following SQL/SPL question.
> }
> } I am pulling a field from a linked database which contains a persons
> } full
> } name, i.e. Frank, Smith Allen. I need a way to split this data into
> } three
> } variables so that I can store the information into three seperate
> } fields
> } within a table in my database. The ideal solution would be to get the
> } owner
> } of the other database to make the change, but unfortunetly, I am on
> } the
> } lower end of that power curve.
> }
> } If any one has any suggestions, I would greatly appreciate them.
> }
> } Nicholas Twerdochlib
> } RTC, Inc.
> } nikel@bellsouth.net
-------------------==== Posted via Deja News ====-----------------------
http://www.dejanews.com/ Search, Read, Post to Usenet