Category: One-liners

Categories
Hints and Tips One-liners Oracle SQL

REGEXP Problem

A good friend asked me how to replace the last full stop (period) in a field with another character using a regular expression e.g. a comma. When I asked “what language?” it turned out he wanted to do this using REGEXP_REPLACE in Oracle SQL.

It’s a problem with a couple of solutions that I could immediately think of – one more “Oracle”, the other more “regular expression”.

First the Oracle solution:

REGEXP_REPLACE({FIELD},'\.',',',INSTR({FIELD},'.',-1))

This uses backward search capability of INSTR() to find position of the last ‘.’ in the field and return it so that the REGEXP_REPLACE only starts the replace from that point on-wards.

Now a “pure” regular expression version:

REGEXP_REPLACE({FIELD},'(\.)([^.]*$)',',\2')

This one is a bit more involved as it uses grouping and a negated character class ([^.] = “not a period”) as well as a back reference (\2).

 

Categories
Hints and Tips One-liners Windows

fsutil one-liner

fsutil fsinfo ntfsinfo X:

where X: is the drive letter gives output like this:

NTFS Volume Serial Number : 0x101051a010518e1a
NTFS Version : 3.1
LFS Version : 2.0
Number Sectors : 0x0000000074498860
Total Clusters : 0x000000000e89310c
Free Clusters : 0x000000000447f222
Total Reserved : 0x00000000000016e2
Bytes Per Sector : 512
Bytes Per Physical Sector : 512
Bytes Per Cluster : 4096
Bytes Per FileRecord Segment : 1024
Clusters Per FileRecord Segment : 0
Mft Valid Data Length : 0x0000000025ec0000
Mft Start Lcn : 0x00000000000c0000
Mft2 Start Lcn : 0x0000000000000002
Mft Zone Start : 0x0000000003cfca20
Mft Zone End : 0x0000000003d00040
Max Device Trim Extent Count : 512
Max Device Trim Byte Count : 0xffffffff
Max Volume Trim Extent Count : 62
Max Volume Trim Byte Count : 0x40000000
Resource Manager Identifier : 5646BA81-xxxx-yyyy-zzzz-185E0F1F2F38

Categories
Hints and Tips One-liners Perl SQL Server Windows

Opening UTF-16LE files in Perl

Placeholder for useful code snippet:

open my $fh, '<:raw:perlio:encoding(UTF-16LE):crlf', $filename

which will convert CR/LF combinations to LF only. Alternatively, to keep them intact:

open my $fh, '<:raw:perlio:encoding(UTF-16LE)', $filename

Useful for reading Windows registry export files, SQL server log export files etc.

Categories
Hints and Tips One-liners SQL

List of Alphabetic Values in SQL Server

I really like this use of the undocumented but widely used master.dbo.spt_values table to give a list of characters e.g. a-z or A-Z:

SELECT CHAR(NUMBER) FROM master.dbo.spt_values
WHERE NUMBER BETWEEN 97 AND 122 --- Lowercase a-z 
--WHERE number between 65 and 90 --- Uppercase A-Z

Other ranges such as “number between 48 and 57” are also useful (that gives 0 through 9).

There are of course other techniques to address this requirement such as creating a permanent table of the numbers/letters required. I’m not advocating the above over others – I just like the “neatness” of the above approach. Perhaps using sys.all_objects or some other documented table/view would be safer – although I doubt microsoft will remove master.dbo.spt_values any time soon. Here’s another approach you might prefer:

SELECT TOP 26 
CHAR(ROW_NUMBER() OVER (ORDER BY object_id)+64) AS upper_case,
CHAR(ROW_NUMBER() OVER (ORDER BY object_id)+96) AS lower_case
FROM sys.all_objects
Categories
Humour One-liners

Some Quotes

Anti-intellectualism has been a constant thread winding its way through our political and cultural life, nurtured by the false notion that democracy means that “my ignorance is just as good as your knowledge” – Isaac Asimov

In the fields of observation chance favours only the prepared mind – Louis Pasteur

The best argument against democracy is a five-minute conversation with the average voter – Winston Churchill

Categories
One-liners SQL Server

Useful SQL Server One-Liners

Quick way to check if your current session is in AUTO-COMMIT mode:
 

1

SELECT (2 & @@OPTIONS)  --- SET IMPLICIT TRANSACTIONS: 0 = OFF, 2 = ON