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

[twenty-six at b-mint.net]

On 02/12/2014 10:45, twenty-six at b-mint.net wrote:
> Hello,
>
> Given varchar(10) data such as:
>
> abc123
> ab1234
> defgh45
> a987654
> dd3
>
> I need a SQL select statement to split this into the alpha portion and
> the numeric portion.   But the alpha  and numeric  parts are of variable
> length which is causing me issues.
>
> Any idea if this is possible?   Can I use a regular expression somehow?
>
> Cheers.
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?