unknown dog

Lots of Nerd Alerts lately, but I haven’t unloaded the camera to see which family pics turned out to be in focus… PROBLEM: Your data has zero or more trailing commas followed by a non-printing character, typically LF/CR. SOLUTION: regexp_replace!

Find leading whitespace: ^[ \t]+ Find trailing whitespace: [ \t]+$

How to find non printing characters using instr and regexp_instr in Oracle databases:

Delete blanks lines from an emacs buffer with the flush-lines command: flush-line ^$.  Sweet!

Two more examples of regexp_like for the memory banks: — convert to NULL if SOME_COL is not A,B,C,1,2,or 3 case when (regexp_like(a.SOME_COL,’[^A-C1-3]‘)) then NULL else a.SOME_COL end as SOME_COL, — convert to 0000 if the col’s value is not a series of valid digits case when (regexp_like (a.ANOTHER_COL,’[^[:digit:]]’)) then ’0000′ else a.ANOTHER_COL end as ANOTHER_COL,

PROBLEM: Identify records where there is non-numeric values for a particular column. SOLUTION: select SOME_COL from SOME_TBL where regexp_like (SOME_COL,’^[^[:digit:]]’) ; Obviously this implies that maybe the field should be a NUMBER datatype to start with, but sometimes what we want, what we should have, and what we actually have are not the same thing.