Tip 29: Pattern Matching With SQL

How can I write a SQL update command to find and modify search strings embedded within character columns?

If you're using SQL Anywhere 5 you can use the new PatIndex function:

The pattern parameter isn't restricted to ordinary characters surrounded by percent signs. It can be something like

    PatIndex ( '%[_][a-z0-9]%', company_name )

to find a single underscore followed by a lowercase character or digit.

Here are some of the rules for building fancy patterns:

   _          Matches any one character.
   %          Matches a string of zero or more characters.
   [string]   Matches any single character in 'string'.
              The 'string' can contain ranges like a-z
              which matches any character from a to z.
   [^string]  Matches any single character not in 'string'.
              The ^ must appear as the first character
              inside the brackets to have this effect.
   [x]        Matches a single character 'x' which can
              be one of _ % [ ] ^.  In other words,
              '[_]' and '_' work differently.
   [x-]       Matches either 'x' or '-'.
   [x%]       Matches either 'x' or '%'.
   [x^]       Matches either 'x' or '^'.

It is a peculiarity of PatIndex that the pattern '%string%' returns the position of 'string' within the search target. In other words, the surrounding '%' characters are treated differently from any '%' characters that might be embedded within the 'string' pattern itself.

Here's an example of how valuable and powerful PatIndex is: Repairing the PowerBuilder Extended Catalog tables. Incorrect use of a product like ERwin can sometimes cause the PBCATCOL label and header values to contain the raw column names themselves. For example, "last_updated" shows up instead of "Last Updated:":

The PatIndex function can be combined with the new "SQL Batch" facility to write a series of commands that regenerate the PBCATCOL label and header values from scratch:

/* PBC_FIX.SQL - Regenerate PBCATCOL Labels and Headers */

/* Before running this from the Database Painter, change     */
/* Preferences - Database - TerminatorCharacter to something */
/* other than a semicolon. A backslash is used here.         */

begin

declare not_done char ( 1 );
declare fix_count integer;
declare loop_count integer ;

/* Initialize the label and header to match the column name. */

update pbcatcol
   set pbc_labl
          = ucase ( left ( pbc_cnam, 1 ) )
          + substr ( pbc_cnam, 2 )
          + ':',
       pbc_hdr
          = ucase ( left ( pbc_cnam, 1 ) )
          + substr ( pbc_cnam, 2 );

/* Optional where clause to exclude labels and headers already filled in...
 where ( ifnull ( pbc_labl, '', trim ( pbc_labl ) ) = '' ) AND
       ( ifnull ( pbc_hdr,  '', trim ( pbc_hdr  ) ) = '' )   ;
*/

/* Strip underscores and capitalize words in labels. */

set fix_count = (
   select count(*)
     from pbcatcol
    where patindex ( '%[_][a-z0-9]%', pbc_labl ) > 0 ) ;

if fix_count > 0 then
   set not_done = 'T';
else
   set not_done = 'F';
end if;

set loop_count = 0;  /* safety limit */

while not_done = 'T' loop

   set loop_count = loop_count + 1;

   update pbcatcol
      set pbc_labl
             = left ( pbc_labl,
                      patindex ( '%[_][a-z0-9]%', pbc_labl ) - 1 )
             + ' '
             + ucase ( substr ( pbc_labl,
                                patindex ( '%[_][a-z0-9]%', pbc_labl ) + 1,
                                1 ) )
             + substr ( pbc_labl,
                        patindex ( '%[_][a-z0-9]%', pbc_labl ) + 2 )
    where locate ( pbc_labl, '_' ) > 0;

   set fix_count = (
      select count(*)
        from pbcatcol
       where patindex ( '%[_][a-z0-9]%', pbc_labl ) > 0 ) ;

   if fix_count <= 0 then
      set not_done = 'F';
   end if;

   if loop_count >= 20 then
      set not_done = 'F';  /* let's stop now */
   end if;

end loop;

/* Strip underscores and capitalize words in headers. */

set fix_count = (
   select count(*)
     from pbcatcol
    where patindex ( '%[_][a-z0-9]%', pbc_hdr ) > 0 ) ;

if fix_count > 0 then
   set not_done = 'T';
else
   set not_done = 'F';
end if;

set loop_count = 0;  /* safety limit */

while not_done = 'T' loop

   set loop_count = loop_count + 1;

   update pbcatcol
      set pbc_hdr
             = left ( pbc_hdr,
                      patindex ( '%[_][a-z0-9]%', pbc_hdr ) - 1 )
             + ' '
             + ucase ( substr ( pbc_hdr,
                                patindex ( '%[_][a-z0-9]%', pbc_hdr ) + 1,
                                1 ) )
             + substr ( pbc_hdr,
                        patindex ( '%[_][a-z0-9]%', pbc_hdr ) + 2 )
    where locate ( pbc_hdr, '_' ) > 0;

   set fix_count = (
      select count(*)
        from pbcatcol
       where patindex ( '%[_][a-z0-9]%', pbc_hdr ) > 0 ) ;

   if fix_count <= 0 then
      set not_done = 'F';
   end if;

   if loop_count >= 20 then
      set not_done = 'F';  /* let's stop now */
   end if;

end loop;

end \

Here's what PBCATCOL looks like after the repairs: