Brothers In Code

More than once I've needed to find all non-ascii characters in a string.  In a .net regular expression this is relatively easy:

[^\x20-\x7F]

I thought that regex would port over reletively easily to Oracle - wrong.

First, I found more than one site reference the \x sequence.  However I just couldn't get it to work.

I then tried a brute force version:

[^A-Za-z 0-9 \.,\?''""!@#\$%\^&\*\(\)\-_=\;:<>\/\\\|\}\{\[\]`~]

To my surprise that didn't work either.  After struggling with it for a while I found the regex reference page from Oracle.  It states:

The following regular expression operators are allowed within the character list, any other metacharacters included in a character list lose their special meaning (are treated as literals):

• Range operator '-'
• POSIX character class [::]
• POSIX collating sequence [. .]
• POSIX character equivalence class [= =]

So in other words, character sets are pretty limited in what they can have in it.  If a backslash is treated as a literal and not as an escape sequence, then that explained why my brute force version didn't work - sort of.  I did eventually get it to work but for some reason I had to remove the ']' within the character class.  So that wasn't perfect either.

Finally I gave it one last shot.  Rather than the hex range, I tried the character range for the same values - from space to tilde (look at an ascii table of values and this will make sense).

[^ -~]

Finally this seemed to work as expected (keep in mind thought that character ranges are affected by the NLS_SORT variable - anything other than binaray, might yield unexpected results).  I ran the following query as a test:

select regexp_replace('This is a test '||chr(191), '[^ -~]', '$') from dual;

The only thing left was to handle non-printable chars like crlf and tabs.  I new the hex code was out so I generated the first ascii character with the chr function to replace the space:

select regexp_replace('This is a test crlf'||chr(10)|| 'blah', '[^' || chr(1) ||'-~]', '$') from dual;

Update: After I wrote this article I found a section on regular-expressions.info that explains some of the oracle differences.

I needed to record the execution time in a stored proc.  I thought this would be straight forward until i realized the resolution on an oracle date datatype is only to the second.  Timestamp has the additional resolution (as well as timezone if  you want it).  Even cooler is that when you subtract two timestamps you get an interval datatype with the difference.  From there, however, I wanted to pass out the value in milliseconds, but that required each component of the interval to be extracted, multiplied, and added together.  Here's some sample code of the whole process.

declare
  v_start timestamp := systimestamp;
  v_end timestamp;
  v_elapsed interval day to second(3);
  v_elapsed_ms number;
  i number;
begin
  for i in 1..100000 loop
    v_elapsed_ms := 0;
  end loop;

  v_end := systimestamp;
  
  v_elapsed := v_end - v_start;
  v_elapsed_ms := 
    ( extract(hour from v_elapsed)* 3600 +
      extract(minute from v_elapsed)* 60 +
      extract(second from v_elapsed) ) * 1000;
  
  dbms_output.put_line('elapsed time:');
  dbms_output.put_line(to_char(v_elapsed));
  dbms_output.put_line(to_char(v_elapsed_ms));
end;

I'm currently converting a winforms app into a WPF app and discovered the key events are significantly different in WPF.  Here's a few tips and things to look out for:

• The old KeyPressEventArgs.KeyChar is just a char and included the modifier key stroke (CTRL-S = 'S'-64 = (Char)19, SHIFT-S = 's'-32 = 'S').
• The new KeyEventArgs.Key is an enum that includes every key.  Modifier keys are their own key stroke.
• (Char)KeyInterop.VirtualKeyFromKey(e.Key) will get you the char value from the new enum, although it is always upper case. 
• Since modifier keys raise an event in WPF, I find I'm ignoring them alot:
  

  
  //ignore modifier keys
  if (e.Key == Key.LeftCtrl ||
          e.Key == Key.RightCtrl ||
          e.Key == Key.LeftAlt ||
          e.Key == Key.RightAlt ||
          e.Key == Key.LeftShift ||
          e.Key == Key.RightShift)
  {
    e.Handled = false;
    return;
  }