SCAN in SQL Who knew? Ok Me

[jamief]

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

this is Bad

Anyway here ya go... Just ran across it and thought it
need to be shared.

SELECT sdsl#, SDEVCM, LOCATE('BAD', UPPER(SDEVCM)   )     
     FROM SLDCOMM                                         
     WHERE LOCATE('BAD', UPPER(SDEVCM)) <> 0 

[MdnghtPgmr]

Re: SCAN in SQL Who New? Ok Me

Jamie,

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

Happy reading...
MdnghtPgmr

[jamief]

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.

   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.

   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.

   SELECT REPLACE('ABCCABCC','ABC','AB')
     FROM SYSIBM.SYSDUMMY1: 

The result is the string 'ABCABC'.

[B.Hauser]

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.

SELECT sdsl#, SDEVCM, LOCATE('BAD', UPPER(SDEVCM)   )     
     FROM SLDCOMM                                         
     WHERE UPPER(SDEVCM) like '%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.

Select *
   from Address
   Where PosStr(Name, '%') > 0  or PosStr(Name, '_') > 0 

Same Query with a Like Predicate:

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:

Select Name, Trim(substr(Name, posstr(Name, ',') + 1)) as LastName,    
             Trim(substr(Name, 1, posstr(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