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

[Roy Hann]

twenty-six at b-mint.net wrote:

> I just came up with this little gem:
>
> select
>
> replace(replace(replace(replace('abc123','1',''),'2',''),'3',''),4,'') 
>   as letter_portion   ,
>
> replace(replace(replace(replace('abc123','a',''),'b',''),'c',''),'d','') 
> as number_portion
>
> Obviously it needs to be expanded for all digits 0-9 and all letters a-z 
> which makes it very horrible.   Can anyone think of something better?

Assuming your strings consist of a run of non-digits (letters) then a
run of digits, and no other pattern, this only needs 10 passes (or
whatever the length of your columns is):

create table strings ( string varchar(10) not null ) \p\g

insert into strings values 
  ('abc123'), 
  ('ab1234'), 
  ('defgh45'), 
  ('a987654'), 
  ('dd3') \p\g

declare global temporary table session.split_strings
(
  alphapart varchar(10) not null,
  digitpart varchar(10) not null 
)
on commit preserve rows
with norecovery; \p\g

insert into session.split_strings (alphapart, digitpart) 
  select '',string from strings \p\g

update session.split_strings
set alphapart = alphapart || left(digitpart,1), digitpart=shift(digitpart,-1)
where digitpart not like '\[0-9\]%' escape '\'
\p\g\g\g\g\g\g\g\g\g\g  --execute it 10 times

select * from session.split_strings \p\g

Roy