Moving my blog

I have recently decided to wind down my blogging on here and have opted to move my blog to www.beeii.com

All the current content has been moved over and in future all new content will be added to the new site. Head over and have a look.

Creating an IsDecimal CLR function for use in TSQL

This morning I saw a SQL Server Central article about the ISNUMERIC function in SQL Server and the fact that it does not always behave the way that people expect it to behave. This reminded me about the first time that I used it and was also surprised when my conversions in my scripts failed after using ISNUMERIC to test for what I believe would be values that would successfully convert to DECIMAL or NUMERIC data types.

As my experience was during some ETL work I promptly decided to use SSIS and a script task in which I could use the VB.NET Isnumeric() function that provided the desired result.

However what would you do if you need this functionality in your stored procs or TSQL scripts. One solution is to create the function that is shown at the end of the article, the other solution would be to use a CLR function written in C#.

The CLR function is really simple to write and the function itself consists mainly of the lines below.

public static SqlBoolean IsDecimal(SqlString Value)
    {
        decimal decValue;

        if (decimal.TryParse(Convert.ToString(Value), out decValue))
        {
            return new SqlBoolean(true);
        }
        else
        {
            return new SqlBoolean(false);
        }
    }

The function will use the TryParse method of the decimal type. After deploying the function I ran a test using the same SELECT statements used in the article to make sure that the CLR function returns the desired result.

Modified SQL statements:

The results were spot on with the behaviour I was looking to achieve:

Ascii Code	Ascii Character	ISNUMERIC Returns	IsDecimal Returns
9		1	0
10		1	0
11		1	0
12		1	0
13		1	0
36	$	1	0
43	+	1	0
44	,	1	0
45	-	1	0
46	.	1	0
48	0	1	1
49	1	1	1
50	2	1	1
51	3	1	1
52	4	1	1
53	5	1	1
54	6	1	1
55	7	1	1
56	8	1	1
57	9	1	1
92	\	1	0
128	€	1	0
160		1	0
162	¢	1	0
163	£	1	0
164	¤	1	0
165	¥	1	0

 

The function also worked great for the last two SELECT statements in the above modified code.

 

If you want to grab a copy of the CLR Project you can download it here.

Simon Munro on Professional Tools

I thought I would share a blog post by Simon Munro which I thought was brilliant! http://simonmunro.com/2010/11/23/professional-tools/

Have a look at the end where he breaks down the cost of a developer using a sub par computer for development. Its always good to see the real cost of decisions and not the window dressing that is so many times held up as the truth.

Enjoy!

Getting the last occurrence of a character in SSIS using FINDSTRING

When looping through a folder containing files to upload it is always much easier to have the For Each Loop container retrieve the filename "Fully Qualified". This allows you to simply specify one variable in the expression used to configure your Flat File Source connection string.

When you then want to include the name of the file being loaded in the data flow you will have to use a Derived Column Transformation to find the last occurrence of the "\" in the string contained in your filename variable. This can be done using a combination of the FINDSTRING and REVERSE functions, after which you then use the RIGHT function to only retrieve the name of the file with its extension.

If we look at the below example of a fully qualified filename you will see that there could be any number of "\" characters when your For Each Loop also traverse sub folder.

C:\Test\Upload\DummyFile.csv

As the FINDSTRING function does not allow built in functionality to allow you to specify that you want the last occurrence we use the REVERSE function to reverse the string and thus give us the first occurrence of the "\" character. After we have determined this we simply subtract the value from the total length of the string and this gives us the position of the last "\" in our fully qualified filename. We then simply use the RIGHT function to retrieve all characters from the last position -1 (remember we want to exclude the character itself from the returned value and only want the filename.). Always remember that you will need to convert your DT_STR (string) datatypes to DT_WSTR (unicode string) as the FINDSTRING expression only works with the latter.

If we use a variable called strFilename the expression will look like this:

RIGHT(@[User::strFilename],(LEN(@[User::strFilename]) - FINDSTRING(REVERSE((DT_WSTR,50)@[User::strFilename]),"\\",1) -1))

This will then bring the filename into the data flow.