ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

A Plethora of Position Finders

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • A Plethora of Position Finders

    Hi Folks

    LOCATE used to be my go-to SQL BIF for finding values in strings. After 7.1 it started throwing errors in certain uses, which I think was to do with BPCS using the "O"pen data type for many of its text fields, so I switched to POSSTR, which didn't suffer from that problem.

    I was aware that many new and exciting things had been added to SQL in 7.1, but time only allowed a few to be sampled, and when one of my customers announced they were going to 7.3, I postponed my investigations to catch up with the whole bulk of changes since 6.1.

    Now I would say that (with apologies to The Three Amigos) we have a plethora of BIFs that seem to do much the same thing. A quick google and perusal of the SQL manual reveal a few choice tidbits of information about the differences, but I haven't yet found a comprehensive survey comparing them.

    Below is a statement that compares them. Use your own data file with a long character field that might have four commas in it (notes, addresses).
    N.B. cast() is only there to fit everything on one screen (SNDESC is 50 long)


    PHP Code:
    select SNDESC,                                                     
     
    castlocate(   ',' ,  SNDESC )    as smallint) as LOCATE      ,  
     
    castlocate(   ',' ,  SNDESC20) as smallint) as LOCATE_20   ,  
     
    castposition',' in SNDESC )    as smallint) as POSITION    ,  
     
    castposstrSNDESC','  )       as smallint) as POSSTR      ,  
    /*            Src     Str  St Inst  LOCATE_IN_STRING aka INSTR   */
    /*            ======  === === ====                               */
     
    castinstrSNDESC','         ) as smallint) as INSTR       ,  
     
    castinstrSNDESC',' ) as smallint) as INSTR_2nd   ,  
     
    castinstrSNDESC',' ,20     ) as smallint) as INSTR_20    ,  
     
    castinstrSNDESC',' ,-1     ) as smallint) as INSTR_LAST     
    from ESN                                                           
    where SNTYPE 
    'P' and SNDESC like '%,%,%,%,%' 

    Provisionally, I would say INSTR() is a clear winner as my new go-to.
    1. It's the shortest (i.e. the INSTR alias for LOCATE_IN_STRING), meaning I'm less likely to have to spill over a line.
    2. It can be as simple as the others (string to search, value to find) or can find "from position", "instance" or reverse (from end).

    My context is basic DB2 for i data in English, EBCDIC. Probably the other functions come into their own in other contexts.

    Any comments from those who have spent more time with these functions?
    Any common scenarios that INSTR/LOCATE_IN_STRING can't handle?
    Are some of these functions only there to provide compatibility with other platforms/standards?
    Are any completely superseded/obsolete?
Working...
X