Numberpart from of a stringfield 2

[stillsteam]

Hello

CR 8.5 or 9
SQL

I need to get numberportion out of a stringfield.
Example data

ID String
1 36yfp/pall, pal 216, rle 1, yfp 6,
2 pal 30, rle 1,
3
4 rle 1,
5 pal 1,

I need to get the numbers after pal and before next comma.

Expected output
ID String
1 216
2 30
3
4
5 1

It's ok if ID 3 and 4 are 0(zero) also. I will use them as numbers in other formulas later on.
There can be any numbers of "strings" within that field between 0 and 10. They are always seperated by a comma if it's not null. As you can see in the sample above then "pal" can be anywhere in that string.
I've been searching alot in this forum but haven't foundanything that solve this completly.

Thank you in advance
Jonas

 

[GJParker]

Try This

If Not IsNull({ar.q_altenhet_generell}) Then  
    If InStr({ar.q_altenhet_generell},'pal ') > 0 Then 
        ToNumber(left(split({ar.q_altenhet_generell},"pal ")[2],instr(split({ar.q_altenhet_generell},"pal ")[2],",")-1))

Gary Parker
MIS Data Analyst
Manchester, England

 

[lbass]

If IsNull({ar.q_altenhet_generell})
Then ""
Else
(
If InStr({ar.q_altenhet_generell},'pal ') > 0
Then left(split({ar.q_altenhet_generell},"pal ")[2],
instr(split({ar.q_altenhet_generell},"pal ")[2],",")-1)
Else ""
)

-LB