ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

SCAN in SQL Who knew? Ok Me

Collapse
This is a sticky topic.
X
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • SCAN in SQL Who knew? Ok Me

    This scans the field (which can contain Upper or Lower case)
    for the word BAD... the locate would return the starting positon
    of the word BAD..

    example LOCATE would return 9
    Code:
    this is Bad
    Anyway here ya go... Just ran across it and thought it
    need to be shared.

    PHP Code:
    SELECT sdsl#, SDEVCM, LOCATE('BAD', UPPER(SDEVCM)   )     
         
    FROM SLDCOMM                                         
         WHERE LOCATE
    ('BAD'UPPER(SDEVCM)) <> 
    All my answers were extracted from the "Big Dummy's Guide to the As400"
    and I take no responsibility for any of them.

    www.code400.com

  • #2
    Re: SCAN in SQL Who New? Ok Me

    Jamie,

    Along those same lines, I recently ran across REPLACE. Very powerful stuff!

    Happy reading...
    MdnghtPgmr
    "Tis better to be thought a fool then to open one's mouth and remove all doubt." - Benjamin Franklin

    Comment


    • #3
      Re: SCAN in SQL Who New? Ok Me

      @#%@#%@#%@#% I feel so ...ummm left behind

      very cool! Thanks

      Example 1: Replace all occurrences of the character 'N' in the string 'DINING' with 'VID'.
      Use the CHAR function to limit the output to 10 bytes.

      PHP Code:
         SELECT CHAR(REPLACE('DINING','N','VID'),10)
           
      FROM SYSIBM.SYSDUMMY1
      The result is the string 'DIVIDIVIDG'.

      Example 2: Replace string 'ABC' in the string 'ABCXYZ' with nothing,
      which is the same as removing'ABC' from the string.

      PHP Code:
         SELECT REPLACE('ABCXYZ','ABC','')
           
      FROM SYSIBM.SYSDUMMY1
      The result is the string 'XYZ'.

      Example 3: Replace string 'ABC' in the string 'ABCCABCC' with 'AB'.
      This example illustrates that the result can still contain the string that
      is to be replaced (in this case, 'ABC') because all occurrences of the string
      to be replaced are identified prior to any replacement.

      PHP Code:
         SELECT REPLACE('ABCCABCC','ABC','AB')
           
      FROM SYSIBM.SYSDUMMY1
      The result is the string 'ABCABC'.
      All my answers were extracted from the "Big Dummy's Guide to the As400"
      and I take no responsibility for any of them.

      www.code400.com

      Comment


      • #4
        Re: SCAN in SQL Who New? Ok Me

        @Jamie,

        instead of using the scalar function LOCATE you also can use the scalar function POSSTR or in your case you simply could use a LIKE predicate in the where clause!
        (adding the column in the SELECT is only necessary if your really need the position.

        PHP Code:
        SELECT sdsl#, SDEVCM, LOCATE('BAD', UPPER(SDEVCM)   )     
             
        FROM SLDCOMM                                         
             WHERE UPPER
        (SDEVCMlike '%BAD%'
        POSSTR and LOCATE allow you easily to scan for % or _ in your String. If you want to do this with LIKE you need an escape.

        PHP Code:
        Select *
           
        from Address
           Where PosStr
        (Name'%') > 0  or PosStr(Name'_') > 
        Same Query with a Like Predicate:
        PHP Code:
        Select *                                 
          
        from Address                           
          Where    Name like 
        '%!%%' Escape '!' 
               
        or  Name like '%?_%' Escape '?' 
        What else you can do with POSSTR or LOCATE is for example to split fields.
        Assumed we have a table where LastName and First Name are in the same field separated by a comma and we wish to splitt this field into to columns.
        You can use the following query:
        PHP Code:
        Select NameTrim(substr(Nameposstr(Name',') + 1)) as LastName,    
                     
        Trim(substr(Name1posstr(Name',') - 1)) as FirstName  
          from Address   
          where coalesce
        (Name' ') <> ' '  and posstr(Name',') > 0      
          order by LastName
        Firstname 
        BTW with release 6.1. the Scalar function LOCATE get an additional parameter that allows you to specify where to start the scan within your string.

        Birgitta

        Comment

        Working...
        X