The missing \b regular expression special character in Oracle.

Oracle's regular expressions don't support the special regular expression character \b, at least not in Oracle 11i.

Consider the following table:

create table tq84_word_boundaries ( txt varchar2(50) ); insert into tq84_word_boundaries values ('AFooABarAndABaz' ); insert into tq84_word_boundaries values ('A FooA BarAndABaz' ); insert into tq84_word_boundaries values ('A Foo, a Bar and a Baz'); insert into tq84_word_boundaries values ('A Foo without a Baz' ); insert into tq84_word_boundaries values ('Foo Bar, Baz' ); insert into tq84_word_boundaries values ('Is it a Foo?' ); insert into tq84_word_boundaries values ('Bar-Foo-Baz' );

Now, I want to find all records that contain the exact word Foo. That is, I want, for example A Foo without a Baz (the fourth record), but I don't want A FooA BarAndABaz (the second record) because FooA is not the exact word Foo

If Oracle supported \b I could use

select * from tq84_word_boundaries where regexp_like(txt, '\bFoo\b');

Yet, Oracle, does not support it and no record is returned.

To improve matters, I could try

select * from tq84_word_boundaries where regexp_like(txt, '\sFoo\s');

This approach returns

TXT -------------------------------------------------- A Foo without a Baz

A bit better, but far from perfect. For example, the fifth record (Foo Bar, Baz) is not returned, because the \s doesn't match start of line. So, I improve the where condition:

select * from tq84_word_boundaries where regexp_like(txt, '(^|\s)Foo($|\s)');

The record is now returned:

TXT -------------------------------------------------- A Foo without a Baz Foo Bar, Baz

Yet again, this is far from perfect. I need also record records where Foo is followed or lead by a non word character (such as ? or -):

select * from tq84_word_boundaries where regexp_like(txt, '(^|\s|\W)Foo($|\s|\W)');

This returns

TXT -------------------------------------------------- A Foo, a Bar and a Baz A Foo without a Baz Foo Bar, Baz Is it a Foo? Bar-Foo-Baz

I think I can live with it.

Links

Source code on github

Little things that make live easier #6: Using clip.exe in cmd.exe to copy somehting into the clipboard

Windows comes with a handy little program, named clip.exe, that can be used to quickly copy something into the clipboard from cmd.exe.

For example, if I wanted to copy the current working directory into the clipboard, I'd go

C:\some\long\and\complicated\path>echo %CD% | clip

Similarly, if I needed to copy all files within the current directory, I'd do

C:\some\path>dir /b | clip

Also, I can copy the content of a file into the clipboard like so:

C:\some\path>clip < someFile.txt

Paginating SQL queries with Oracle 12c

Oracle 12c not only allows to create TOP N queries with an understandable syntax, the row limiting clause also makes it possible to paginate a result set:

create table tq84_table ( id number, txt varchar2(10) ); insert into tq84_table values ( 2, 'two' ); insert into tq84_table values ( 1, 'one' ); insert into tq84_table values ( 6, 'six' ); insert into tq84_table values ( 8, 'eight'); insert into tq84_table values ( 3, 'three'); insert into tq84_table values ( 7, 'seven'); insert into tq84_table values ( 4, 'four' ); insert into tq84_table values ( 9, 'nine' ); insert into tq84_table values (10, 'ten' ); insert into tq84_table values ( 5, 'five' );

select id, txt from tq84_table order by id offset 4 rows -- skip first 4 records fetch next 3 rows only -- fetch next 3 records ;

returns

ID TXT ---------- ---------- 5 five 6 six 7 seven

Links

SQL statement on github

TOP N select queries with Oracle 12c

With Oracle 12c, it's possible, finally, to do TOP n select queries with an easily understandable syntax.

Here's a table to demonstrate it:

create table tq84_table ( id number, txt varchar2(10) ); insert into tq84_table values ( 2, 'two' ); insert into tq84_table values ( 1, 'one' ); insert into tq84_table values ( 6, 'six' ); insert into tq84_table values ( 8, 'eight'); insert into tq84_table values ( 3, 'three'); insert into tq84_table values ( 7, 'seven'); insert into tq84_table values ( 4, 'four' ); insert into tq84_table values ( 0, 'zero' ); insert into tq84_table values ( 9, 'nine' ); insert into tq84_table values (10, 'ten' ); insert into tq84_table values ( 5, 'five' ); commit;

Now, a select statement with fetch first row only:

select id, txt from tq84_table order by id fetch first row only;

selects

ID TXT ---------- ---------- 0 zero

Links

Paginating SQL queries with Oracle 12c

SQL on github

Little things that make live easier #5: Navigation the Windows Registry with Powershell

It's possible to navigate the Windows Registry with Powershell

PS C:\> cd HKLM:/SOFTWARE/Microsoft/PowerShell PS HKLM:\SOFTWARE\Microsoft\PowerShell> ls Hive: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell SKC VC Name Property --- -- ---- -------- 4 2 1 {Install, PID} PS HKLM:\SOFTWARE\Microsoft\PowerShell> cd 1 PS HKLM:\SOFTWARE\Microsoft\PowerShell\1> ls Hive: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell\1 SKC VC Name Property --- -- ---- -------- 0 1 0407 {Install} 0 6 PowerShellEngine {ApplicationBase, PSCompatibleVersion, RuntimeVersion, ConsoleHostAssemblyName...} 1 0 PSConfigurationProviders {} 2 0 ShellIds {}

Little things that make live easier #4: writing colored text in cmd.exe

A constant source of frustration when using cmd.exe is that it seems impossible to write colored text easily, unlike other shells (although I don't consider cmd.exe to be a shell). Yet, if powershell is installed, then it's possible to write such colored text. Here's a powershell script (c:\temp\colors.ps1):

write-host "" write-host " " -noNewLine write-host "Warning" -foregroundColor red -backgroundColor yellow write-host " " -noNewLine write-host "-------" -foregroundColor blue write-host " Wearing headphones doesn't" -foregroundColor red write-host " make farts silent." -foregroundColor red

When executed with something like

powershell -executionpolicy bypass c:/temp/colors.ps1

It prints

Using Exporter in a Perl module

The Exporter module makes it easy for another module to define which functions are exported into the other module's users' namespace.

Here's a simple module to demonstrate that

package SomeModule; use warnings; use strict; use Exporter; use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS); @ISA = qw(Exporter); $VERSION = 1; @EXPORT = qw(exp_one exp_two); @EXPORT_OK = qw(ok_one ok_two ok_three ok_four); %EXPORT_TAGS = ('all' => [ ( @EXPORT, @EXPORT_OK) ], 'some' => [ qw(ok_one ok_three exp_two) ] ); sub exp_one { return 'exp_one'; } sub exp_two { return 'exp_two'; } sub ok_one { return 'ok_one'; } sub ok_two { return 'ok_two'; } sub ok_three { return 'ok_three';} sub ok_four { return 'ok_four'; } 1;

And here's a script that uses SomeModule:

use warnings; use strict; use feature 'say'; use SomeModule; say exp_one(); say exp_two(); # say ok_one();

This script can call exp_one and exp_two from SomeModule because these two functions are listed in @EXPORT. Functions listed in @EXPORT are by default exported into the user's namespace.

ok_one, on the other hand, cannot be called directly as it is not listed in @EXPORT. It can, however, be called so SomeModule::ok_one().

Here's another script:

use warnings; use strict; use feature 'say'; use SomeModule qw(ok_one ok_two); # say exp_one(); say ok_one(); say ok_two();

Now, I specify the identifiers I want to have imported into my namespace (qw(ok_one ok_two)). Accordingly, I can call these.

I cannot call exp_one (which I could call in the first script), because as soon as I begin specifying identifiers to be exported I need to specify all of them.

Of course, it will soon be tiresome to always indicate which functions I want to have imported. Therefore, I can define tags with the %EXPORT_TAGS hash that groups multiple identifiers under a tag. For example, the tag all imports all functions. In my case, I just used the combined values of @EXPORT and @EXPORT_OK. This is demonstrated with the third script:

use warnings; use strict; use feature 'say'; use SomeModule (':all'); say exp_one(); say ok_one(); say ok_two();

Lastly, I can import some functions, too:

use warnings; use strict; use feature 'say'; use SomeModule (':some'); # say exp_one(); say ok_one(); say exp_two();

Links

Source code on github: SomeModule.pm, script.pl, script_2.pl, script_3.pl and script_4.pl