lacking a regexp extension compiled in my version of sqlite3, how can I SELECT foo FROM table WHERE Instr(foo, /[a-zA-Z]/) > 0 (or something similar). That is, identify all rows that might contain any non-numeric character?

You could check to make sure length(foo) > 0 then use the replace() function to remove all digits 0-9.

If foo only contained digits then the length() after replacement will be zero, so if the length() isn't zero there must be non-digits present.

Something like:

SELECT foo
  FROM tbl
 WHERE length(foo) > 0
   AND length(replace(replace(replace(
              replace(replace(replace(
              replace(replace(replace(replace(foo,
       '9', ''), '8', ''), '7', ''),
       '6', ''), '5', ''), '4', ''),
       '3', ''), '2', ''), '1', ''), '0', '')) > 0;

Thank you, I would have preferred to replace that with something simpler, but it is indeed very cleverly done, ahem. It works well.

Actually, on second thought, we can replace all those replace() with a trim():

SELECT foo
  FROM tbl
 WHERE length(foo) > 0
   AND length(trim(foo, '0123456789')) > 0;

that would not work because Trim() removes the specified chars from either end of the string. Here is an example of a fail

> SELECT Trim('098ahif792hsk910', '0123456789');
ahif792hsk

The built-in glob operator understands negated character classes:

   select foo from tbl
       where foo glob '*[^0-9]*';

this is wonderful, thanks! I didn't know about glob and now it seems I can do much with it that I would with a regexp extension

I had something similar to this at work when perusing a computed column. Someone wanted to sentence case station names, so used this method. Replacing various connotations of alphabetic characters resulted in about 78 of these replace statements. It wasn't pretty ... it doesn't scale well.

it would be nice if there were a factory-supplied regexp capability, maybe via a compile time option, just like json, rtree, fts5, etc.