RE: Informix-sql beginner question
Don't know about yours. but my SPL REALLY hates any variable as a string
subscript. This look kludgy, but works nicely:
create procedure fname (name char(255))
returning char(255);define name2 varchar(255);
define i smallint;
let i = length(name);
let name2 = null;
while name[1,1] != "/" and i > 0
if name2 is null then
let name2 = name[1,1];
else
let name2 = name2 || name[1,1];
end if
let name = name[2,255];
let i = i - 1;
end while
return name2;
end procedure;
> -----Original Message-----
> From: Ramdas Gopinath [SMTP:rgopi@iname.com]
> Sent: Monday, October 25, 1999 8:38 AM
> To: informix-list@iiug.org
> Subject: Re: Informix-sql beginner question
>
>
> Hi Steve,
> You will have to write a Stored Procedure in order to retrieve either the
> First Name or the last name in a Query.
> It will be a lengthy SPL but it should work fine.
> Assuming that the Length of the NAME column is 50 characters, you will
> have
> to check the position (POS) of the '/' and then return the string from 1
> to
> POS-1 for the first name or POS+1 to 50 for the last name.
> Your SPL could be as follows:
>
> create procedure sp_first_name(name char(50) returning char(50))> define POS smallint
>
> let POS = 0
>
> if name(1,1) = '/'
> then
> let POS=1
> end if
>
> if name(2,2) = '/'
> then
> let POS=2
> end if
>
> ..... until name(50,50)
>
> if POS=1
> then
> return null # That means no First name given
> else
> return name(1,POS-1) # returns first name
> end if
>
> end procedure
>
> Create another procedure just like the one above for Last name, the only
> difference will be that the return will be
> return name(POS+1, 50)
>
> HTH,
> Gopi
>
> Steve Cormier <scormier@idirect.com> wrote in message
> news:sJYQ3.13242$jF3.95249@quark.idirect.com...
> > I have data in a column, seperated by a slash ( / ) which holds customer
> > names.
> > For example Smith/Bob/Mr
> >
> > The slash is always in a different location.
> >
> > How can I write a select statement to retrieve only the first name
> > or only the last name ?
> >
> > Thanks Alot
> >
> >
> >
> >
>