Identify position of first letter in string

[RDKells]

Hello,

I am trying to split the below string into columns via an SQL query, where;
Column 1 is the 2 digit number either at the end of the string or after the first set of numbers (they are the same)
Column 2 is the number at the start of the string
Column 3 is the text

The data format is consistent; number, spaces, 2 digits number, space, text, space, hyphen, space, 2 digit number

Column 3 is where I am struggling, example;

ATABLE;

AFIELD
0062    36 some text i need - 36
00156    70 further text i need - 70               
0263    01 please give me this text ahhh - 01

This is what I have so far, to get the first 2 columns;

SELECT SUBSTR(AFIELD, LOCATE('-', AFIELD) + 2, 2) Column1,
       SUBSTR(AFIELD, 1, LOCATE(' ', AFIELD) -1) Column2  
  FROM ATABLE

I can't think of a way to locate the first occurrence of text, or maybe there's a different way? I've had a look through the DB2 documentation for scalar functions to see if there's anything to help me and I can't seem to find one - unfortunately I can't use REGEX as not all of our machines have the PTFs installed.

Any help appreciated.

Cheers,
Ryan

[Vectorspace]

To do this entirely within an SQL query, I have one idea but it isn't pretty

use substr(afield,locate(' ',afield)) to return afield starting after the first number. So it starts at the first set of spaces
trim() that to drop the first set of spaces, so it starts with the second number
repeat that again to drop the second number and second space
the string now starts with the text, extract from there to the hyphen

You may have to use a subselect to make it clear

[code]
select trim(substr(field3Plus,1,locate('-',field3Plus)-1)) as textField
from table(select afield, trim(substr(field2Plus,locate(' ',field2Plus))) as field3Plus -- field3Plus = afield without first/second number/space block - starts with text
from table (select afield, trim(substr(afield,locate(' ',afield))) as field2Plus -- field2Plus = afield without first number and first space block
from atable
) atable2
) atable3
[code]

It would be much neater if you write a helper program that could be called like an SQL function, specifically for this process

[RDKells]

This example is just 3 columns, there are numerous in the actual string - I was hoping there was some existing SQL functions that could help but it seems the statement would be 2342342423 lines and it would be better to, as you said, write a helper program to do the grunt work.

Thanks for the help

[JonBoy]

Why not write it in RPG as a stored procedure and then use it from SQL. As a programming language SQL is definitely not the answer to everything, Seems to me that in RPG this would be pretty trivial.