Converting CLng in MS Access SQL to T-SQL (SQL Server)

The problem I need to solve is how do I reproduce the exact functionality of the CLng (function built-in to MS Access SQL) in MS SQL Sever. The answer is not so difficult, but knowing what the question to ask was not so obvious.

After much frustration I finally realized that how CLng in MS Access SQL works is actually quite unintuitive. My situation can be explained quite simply. I have a query in MS Access that I need to convert to run completely on SQL Server. In parts of the MS Access queries the CLng function is used. CLng is similar to Round() in SQL Server, but is not the same particularly when it comes to values that end in .5. Initially I thought it would just truncate the decimals of a double. For example, I thought CLng(.5) should be 1, but MS Access says it is 0. Interestingly, CLng(3.5) is 4, not 3 and CLng(2.5) is 2, not 3. This behavior is very inconsistent to me, but I need to reproduce it on SQL Server using T-SQL so that I can compare the queries.

After some reading about issues related to rounding in general on Wikipedia I found out that there are many ways to round. After reading a technical article from Microsoft I found out that indeed VBA (and appears to be MS Access SQL) do not round the same as MS SQL Server. From this I learned the key to the solution; the CLng function in MS Access implements rounding using the Banker’s Rounding method described in both of the above sources. I did find an implementation of Banker’s Rounding once I knew what to search for. I found a good solution here. I have copy and pasted the solution below.

Here is that function you can execute on SQL Server that will do the same thing as Clng on MS Access SQL because it is an implementation of Banker’s Rounding.

create function MSAccessClng(@x as float)
returns int
as
BEGIN
Declare @DecimalPlaces as tinyint
Set @DecimalPlaces = 0

set @x = @x * power(10, @DecimalPlaces)

return
  case when @x = floor(@x) then @x
  else
    case sign(ceiling(@x) - 2*@x + floor(@x))
    when 1 then floor(@x)
    when -1 then ceiling(@x)
    else 2*round(@x/2,0) end
  end / power(10, @DecimalPlaces)
END

Now you have a function you can use on SQL Server just like you have on MS Access. Keep in mind this is a Scalar-valued function and this means there is a performance penalty for using it, but if you need this functionality you need the functionality.

Below are different examples of what I used to test that the functionality is the same on MS Access and MS SQL Server (T-SQL). Notice, the ones that say SAME means that CLng() has the same functionality as cast(). The ones that say DIFFERENCE means that the special logic was needed to implement differences. You can also see the usage of the function.

-- SAME
select clng(-3.3) -- -3
select cast(-3.3 as int) -- -3
select Round(-3.3, 0) -- 3
select dbo.MSAccessClng(-3.3) -- -3

-- SAME
select clng(-0.3) -- 0
select cast(-0.3 as int) -- 0
select round(-.3,0) -- 0
select dbo.MSAccessClng(-0.3) -- 0

-- DIFFERENCE
select clng(-3.5) -- -4
select cast(-3.5 as int) -- -3
select Round(-3.5,0) -- -4
select dbo.MSAccessClng(-3.5) -- -4

-- SAME
select clng(-0.5) -- 0
select cast(-0.5 as int) -- 0
select round(-0.5, 0) -- error
select dbo.MSAccessClng(-0.5) -- -0

-- DIFFERENCE
select clng(-0.51) -- -1
select cast(-0.51 as int) -- 0
select round(-0.51, 0) -- error
select dbo.MSAccessClng(-0.51) -- -1

-- DIFFERENCE
select clng(-0.8) -- -1
select cast(-0.8 as int) -- 0
select round(-0.8, 0) -- error
select dbo.MSAccessClng(-0.8) -- -1

-- SAME
select clng(-1) -- -1
select cast(-1 as int) -- -1
select round(-1, 0) -- -1
select dbo.MSAccessClng(-1) -- -1

-- SAME
select clng(-100000) -- -10000
select cast(-100000 as int) -- -10000
select round(-100000, 0) -- -10000
select dbo.MSAccessClng(-100000) -- -10000

-- SAME
select clng(3.3) -- 3
select cast(3.3 as int) -- 3
select Round(3.3, 0) -- 3
select dbo.MSAccessClng(3.3) -- 3

-- SAME
select clng(0.3) -- 0
select cast(0.3 as int) -- 0
select round(0.3,0) -- 0
select dbo.MSAccessClng(0.3) -- 0

-- DIFFERENCE
select clng(3.5) -- 4
select cast(3.5 as int) -- 3
select Round(3.5,0) -- 4
select dbo.MSAccessClng(3.5) -- 4

-- SAME
select clng(0.5) -- 0
select cast(0.5 as int) -- 0
select round(0.5, 0) -- error
select dbo.MSAccessClng(0.5) -- 0

-- DIFFERENCE
select clng(0.51) -- 1
select cast(0.51 as int) -- 0
select round(0.51, 0) -- error
select dbo.MSAccessClng(0.51) -- 1

-- DIFFERENCE
select clng(0.8) -- 1
select cast(0.8 as int) -- 0
select round(0.8, 0) -- error
select dbo.MSAccessClng(0.8) -- 1

-- SAME
select clng(1) -- 1
select cast(1 as int) -- 1
select round(1, 0) -- 1
select dbo.MSAccessClng(1) – 1

-- DIFFERENCE
select clng(34.5) -- 34
select cast(34.5 as int) -- 34
select Round(34.5,0) -- 35
select dbo.MSAccessClng(34.5) -- 4

-- DIFFERENCE
select clng(0) -- 0
select cast(0 as int) -- 0
select round(0, 0) -- 0
select dbo.MSAccessClng(0) – 0

-- SAME
select clng(100000) -- 10000
select cast(100000 as int) -- 10000
select round(100000, 0) -- 10000
select dbo.MSAccessClng(100000) -- 10000

Get First or Second Word using T-SQL

When working with SQL (T-SQL) I often need to work with words with-in a string (nvarchar or varchar). For instance I want to display only the first word (first name in this case) of a field (FullName in this case) or maybe the second word in other cases. Words are funny things, they can be separated by commas, spaces, hyphens, and other characters such as semi-colons, exclamation point, parenthesis, brackets perhaps.

Below is the code to create a Scalar-valued Functions in SQL Server (T-SQL) that get the first word or second word.

 

Get First Word

create function GetFirstWord(@Name as nvarchar(100))
returns nvarchar(100)
as
BEGIN

Declare @FirstWord as nvarchar(100)
Declare @SpaceIdx as int

-- convert word separators to space for simplicity
Set @Name = Replace(@Name, ',', ' ') -- comma to space
Set @Name = Replace(@Name, '.', ' ') -- period to space
Set @Name = Replace(@Name, '-', ' ') -- hyphen to space

Set @SpaceIdx = CHARINDEX(' ', @Name)

if (@SpaceIdx > 0)
    SET @FirstWord = SUBSTRING(@Name, 0, @SpaceIdx)
else -- all one word
    SET @FirstWord = @Name

return @FirstWord;
END;

 

Get Second Word

create function GetSecondWord(@Name as nvarchar(100))
returns nvarchar(100)
as
BEGIN

Declare @SecondWord as nvarchar(100)
Declare @AfterWord1 as nvarchar(100)
Declare @SpaceIdx as int
Declare @Space2Idx as int

-- convert word separators to space for simplicity
Set @Name = Replace(@Name, ',', ' ') -- comma to space
Set @Name = Replace(@Name, '.', ' ') -- period to space
Set @Name = Replace(@Name, '-', ' ') -- hyphen to space

Set @SpaceIdx = CHARINDEX(' ', @Name)

if (@SpaceIdx = 0) return ''

Set @AfterWord1 = SUBSTRING(@Name, @SpaceIdx+1, len(@Name) - @SpaceIdx)

Set @Space2Idx = CHARINDEX(' ', @AfterWord1)

if (@Space2Idx > 0)
    SET @SecondWord = SUBSTRING(@AfterWord1, 0, @Space2Idx)
else -- remainder of word
    SET @SecondWord = @AfterWord1

-- if second word is the same as the original, then we only have one word
if @SecondWord = @Name return ''

return @SecondWord;
END;

Usage

select GetFirstWord(‘Joe Smith’)

returns: Joe

select GetSecondWord(‘Joe Smith’)

returns: Smith

 

Extending the Code

In the example above was just concerned with with commas, spaces, and hyphens, but you can extend the code quite easily by adding additional replace statements.

 

Note on Performance

I have implemented these functions as Scalar-valued functions which are known to incur a noticeable performance hit when you use them in a select statement. 

Efficiently Counting Spaces using T-SQL

Let’s assume you have a table called Person and it has a column called FullName. As you might expect, the FullName is really just a concatenation of the persons first and last name separated by a space. If I want to get some stats on how many people have only one space (implying two words only) it is actually quite easy to figure this out. Consider the following SQL statement

 

select FullName from Person

Let pretend it returns something like

Ben Wheel
James Johnson Jr.
Jane Dela Cruz
Jeff Jones
Lance Strong

Exactly 1 Space

To get all rows that have exactly one space in them you could do the following

select FullName from Person
where len(FullName) - len(Replace(FullName, ' ', '')) = 1

Jeff Jones
Lance Strong
Ben Wheel

Exactly 2 Space

To get all rows that have exactly one space in them you could do the following

select FullName from Person
where len(FullName) - len(Replace(FullName, ' ', '')) = 2

James Johnson Jr.
Jane Dela Cruz

Less than or equal to 3 Space

To get all rows that have exactly one space in them you could do the following

select FullName from Person
where len(FullName) - len(Replace(FullName, ' ', '')) <= 3

Ben Wheel
James Johnson Jr.
Jane Dela Cruz
Jeff Jones
Lance Strong

As you can see it is relatively easy to count occurrences of a character using T-SQL. You can expand on these queries just by changing the stuff in red.

Open 2 Excel Files in separate windows

WARNING: Changing your registry can be dangerous to the health of your computer. Do so at your own risk.

This change makes it so when you click an Excel file it will ALWAYS open in a new window (a different instance of Excel)

 

.XLSX

1. I highly recommend you back up your registry before making any changes.
2. Open regedit.exe
3. Navigate to HKEY_CLASSES_ROOT/Excel.Sheet.12/shell/Open/command
4. Open (Double-click) the key called (Default)
5. Change the value from
         "C:\Program Files (x86)\Microsoft Office\Office12\EXCEL.EXE" /e
   to "C:\Program Files (x86)\Microsoft Office\Office12\EXCEL.EXE" /e “%1”
6. Click the OK button
7. Below the (Default) key you will see a key with the name command.
   Right click it and select Rename.
   Change the name to something else (it doesn’t matter what)
8. Your screen should now look something like the following (I appended bv to the command key, but you should probably use something different)
   
   
   
9. Navigate to HKEY_CLASSES_ROOT/Excel.Sheet.12/shell/Open/ddeexec
   Right click it and select Rename.
   Change the name to something else (it doesn’t matter what)
10. Your screen should now look something like the following:
    
    
    
11. You have now successfully made it so that when you open an existing Excel file (that has the extension .xlsx) it will open in a new window. This allows you to easily compare or view two Excel file at the same time. This is particular helpful if you have two monitors. You can also ways revert back if you decide you don’t like the change by undoing the changes made here, that is why we renamed instead of deleted the keys.

.XLS

You can follow the same instructions for .XLS except you will want to amend the instruction so that where ever you see Excel.Sheet.12, replace it with Excel.Sheet.8.

BTW, I found this tip here.

PNG Compression Tools

PNG is a great format, but not all applications that save them create the same size files. There are at least four major tools out there that do nothing more than optimize the size using lossless techniques. Some of these tools even work on other formats also.

I have not personally benchmarked them, but this person has. Below are some excerpts from their post.

 

• punypng: the new kid of the block.
• smush.it: Uses pngcrush as the main PNG optimizer.  Currently, available in Yahoo’s YSlow Firefox plug-in.  I believe it uses the -brute option for pngcrush.
• OptiPNG: A slighlty better algorithm, compared to pngcrush.  In case you’re wondering, Google’s PageSpeed plugin also uses OptiPNG for it’s compression library.
• ImageOptim: The heavyweight contender.  Available for OS X, ImageOptim uses every major library out there: advdef, pngcrush, optipng, pngcrush, jpegoptim, jpegtran, and optionally pngout.  I ran this the benchmarks with pngout enabled.

 

If file size is critical I suggest trying all of them since it seems one does better than the other depending on the file.

I use Paint.NET a lot and there is a plug-in for it if you use OptiPNG.

If you want to use Visual Studio, there is a plug-in for it that uses punypng and smush.it.

A look at the topic of Artificial Intelligence (AI)

General AI

Artificial Intelligence Wiki – a good place to start to understand what AI encompasses and its different areas.

Journal of Machine Learning Research – Amazingly complex scientific papers of AI algorithms. Not for light reading.

Machine Learning Open Source Software

 

Areas of interest for me:

Reinforcement Learning – concerned with how an agent ought to take actions in an environment so as to maximize some notion of cumulative reward.

Data Mining

Data Mining – good starting point for learning about data mining

Text Mining

Basically Text Mining is data mining on unstructured data like documents, web pages, etc. instead of a database.

Natural Language Processing – Imagine a machine being able to scour the internet and actually extract knowledge about what it read.

Text Mining – an area of Natural Language Processing, has many commercial uses even today. i.e. Bing

Semantic Web – a machine readable version of the web.

DBpedia – An effort to translate Wikipedia into machine understandable format to facilitate complex queries and meanings of words, not just matches.

Freebase – Similar to DBpedia, but it is hand crafted. DBpedia has lots of links to it as well. A database of “bar codes” for all entities on the web. Aliasing… Also powers Bing

What is Text Mining – describes how text mining is more complex than data mining since it involves natural language processing.

Carrot2 – text and search results clustering framework. Very cool way to browse search results and get to what you are looking for

Reading a MS Word 2007 Document in .docx format using C#

If you want need to read (or write) from a MS Word 2007 Document that has been saved in the Open XML format (.docx) then you can use the Open XML SDK 2.0 for Microsoft Office to do just. The first thing you will need to do is download and install the SDK. In particular, you must download and install the OpenXMLSDKv2.msi. In addition, you can download the OpenXMLSDKTool.msi if you want. It has some VERY nice features like generating code from an existing .docx file.

Now that you have the files you need, open Visual Studio (2008 or 2010 works fine), open the project you want to use, and add a reference to the DocumentFormat.OpenXml (I had to browse to it in the adding references windows by going to C:\Program Files (x86)\Open XML SDK\V2.0\lib) and WindowsBase (mine was located in the list of .NET tab when adding references). Please note, this code does not require MS Word be installed and is safe to run on the server such as with ASP.NET.

Now that you have the api, the rest is just working with the document. To get a better understand on how to work with the parts (structure) of the Word Document, click here.  For a list of “How do I…” code samples, click here.

Here is example code on how to get the body of the document.

using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Wordprocessing;

…

using (WordprocessingDocument wordDocument = WordprocessingDocument.Open(Filename, false))
{
        var body = wordDocument.MainDocumentPart.Document.Body;
}

 

Here is an example of a more complex line of code that can be used to navigate the structure of the document using LINQ. In this case the document has a table in it and we are getting the first row and first cell of that row and the second element.

wordDocument.MainDocumentPart.Document.Body.ChildElements.First<Table>().ChildElements.First<TableRow>().ChildElements.First<TableCell>().ElementAt(2).InnerText

I hope this gives you an idea of how to get started. There are lots of good links, examples, etc here.