[Info-ingres] Help splitting up a varchar into pieces

[Ingres Forums]

If there's a simple way to do this in Ingres, I'm struggling to think of
it.
Using a row producing procedure may be the best solution (see below).
Cheers, GJ

create table vtable(col1 varchar(10));
insert into vtable
values('abc123'),('ab1234'),('defgh45'),('a987654'),('dd3');

create procedure rpproc(p varchar(10)) result row
(varchar(10),varchar(10))=
declare l=integer;
begin
l=0;
while (l<length(p) and charextract(p,l+1) > '9')
do
l=l+1;
endwhile;
return row (left(p,l), right(p,length(p)-l));
end;

select v.col1,p.* from vtable v, rpproc(v.col1) p;


-- 
geraintjones
------------------------------------------------------------------------
geraintjones's Profile: http://community.actian.com/forum/member.php?userid=57763
View this thread: http://community.actian.com/forum/showthread.php?t=16324