Search code examples
sqlibm-midrangedb2-400

DB2 for IBM i (AS400) query substr

I have 48/50 g in a as400 database and I want to (query)substr before the "/" and before the space between 50 and g.

Result I should get is 50

I'm searching for hours now and try and try but no luck.

any help is welcome.

Thanks!

Solution

  • You want to use the LOCATE() function...
    Assuming that the first space is the one you want the following will work:

    --create variable mystr char(10) default('48/50 g') 
select substr(mystr                 
              , locate('/'          
                       , mystr) + 1 
              , locate(' '          
                       , mystr)     
                 - (locate('/'       
                           , mystr) + 1)  
              )                     
from sysibm.sysdummy1

    However, if this is an often needed function. Consider creating a user defined function (UDF) that will parse and split the string in this manner.