TSQL – Retrieve only numbers in a string

With related to my earlier post which demonstrated how to retrieve only numbers in a string using C#, this is how you can do it using TSQL in SQL Server. This will be also become handy if you want to do this in a backend process (SP, Trigger, etc). This is a UDF which you can reuse in your TSQL codes.

/****** Object:  UserDefinedFunction [dbo].[fn_filternumbers]    Script Date: 11/27/2009 17:59:56 ******/

ALTER FUNCTION [dbo].[fn_filternumbers]
@Src nvarchar(255)
RETURNS nvarchar(255)

declare @Res nvarchar(255)
declare @i int, @l int, @c char
select @i=1, @l=len(@Src)
SET @Res = ''
while @i<=@l
   set @c=upper(substring(@Src,@i,1))
    IF  @c IN ('0','1','2','3','4','5','6','7','8','9')
        SET @Res = @Res + @c
   set @i=@i+1


You can use this function in the following manner;

select  telephone, dbo.fn_filternumbers(telephone) from users

This will return telephone numbers by removing white spaces, characters and will only return numbers. (i.e +94 (475) 849 984 would return 94475849984)

Please feel free to drop a comment if you have any question on this.


C# – Retrieve only numbers in a string

I wanted to retrieve only the number in a string to clean up some telephone numbers users have entered in a system today. So I came across with this nice Regular Expression it is really simple and most importantly it was the fastest method I could found. Here is the code;

public String returnonlyNumbers(String stringValue)
           return string.Join(null, System.Text.RegularExpressions.Regex.Split(stringValue, "[^\\d]"));

The above code would return only the numbers in any given string. For example if you pass the string  “+94 (084) 849748 489748”  to this method it would return “94084849748489748”.

Hope someone would find this beneficial.


Ravana the Great on the Canvas

These are some paintings of King Ravana by Dhammika Ravindra Dissanayaka. The history has been very rude on King Ravana, but Dhammika has seen him in another dimension. It is really a great work since we have very few texts about the costumes of that time. Here are few paintings from his Exhibition which was concluded in Lionel Wendt on 22nd November 2009.

Time Travel on the Web

Time travelling is one of the most fascinating concepts in the world of science fictions. Is time travel possible in the real world? This is a question which most of us would have asked from our selves at least once in our life. Anyway it will be a question without a proper answer for another few decades or more.

At least it has become a reality for the web. Yes you can do travel through time in the web. For a example if you want to go to yahoo.com as it was in 1998, it is really possible  with the Way Back Machine. The internet archive a non-profit organization is having the worlds biggest digital library of web archives. Apart from the way back machine they also do provide various other tools to analyze the web archive for researchers, students and general public for free.

So go ahead and play around with the Way Back Machine http://web.archive.org/collections/web/advanced.html


Download Torrents without a Torrent Client

Yes it is! You read it correctly, you can now download Torrents as HTTP for free. KickassTorrents is one of the fastest growing Torrent search engines offers Http Torrent downloads for free. Apart from this magnificent feature this site is very user friendly and most importantly it is simple.
Hope most of you will enjoy this Torrent Search Engine in many more months to come.

When the Go gets Going (Google Go)

Google the search engine giant has introduced its newest programing language called "Go". This is the second programing language Google has released in the recent past. In july this year they released the language called Simple.

Go is a system language like C, C++ but as they claim it is an extremely fast for development like Python. Go works with Google's open-source technology Native Client, designed for running native code in web-based applications, but it is not known yet whether Go will be used in the new Google operating system, Chrome.

Here is the URL for Go language's official website http://www.golang.org. You will find tutorials, samples, compiler downloads and many more in this site.

Here is a video showing Go on the Go:


MS CRM 4 - Hiding System (Public) Views

In CRM 3 when we want to hide a System view, we were use to hide it by sharing that view with a team which has no users.

But in CRM 4 Microsoft has removed this functionality of sharing views (Brilliant isn’t it). This leaves us no option to hide unwanted System views using CRM Customization. What is more painful is there is no way we can delete these system views either.

But as always there are two workarounds we will achieve this goal. Out of those two easiest and quickest one is updating the SavedQuerybase table in the MSCRM database. Even though it is not recommended by Microsoft this would be very beneficial when you are in a tight schedule. Now let’s see how we can do this.

First you need to find the particular view which you want to edit from the SavedQuerybase table. For that you can use the following query by changing the "systemviewname" to the particular view name you want to edit.

select * From SavedQuerybase where name like '%systemviewname%'

Then you must update the "isprivate" flag in this table to 1. The default is 0 which means the view is public. For the update you can use the following query. The 'systemviewname' must be changed to the exact name of your view which you could find using the earlier query’s result set (column name is “name”).

update SavedQuerybase
set isprivate = 1
where name = 'systemviewname'

This will remove that particular system view from user’s dropdown list.

Anyway there is another way of achieving this in a proper way by writing a CRM plug-in to change the SavedQuery entity. This needs some coding and of course deployment. Here are two great articles which will walk you through the process;


If you are interested about learning more about CRM 4 development tricks, I strongly recommend buying following books;

Will Doomsday 2012 be a reality? - 10 Failed Doomsday Prophecies

The whole world (At least most of us) is wondering about the Doomsday prophecy in 2012. The main reason is the end of Mayan's long calender is due on 21st of December 2012. I am not going to argue on whether it is false or not. Purely because of there are millions of articles on the web on this.

Anyway today I found a very interesting article in www.nationalgeographic.com on 10 failed such propecies in the history. What is more interesting is all these are not from very distance past. Just have a look, you may be able to have some breathing space if you were so scared of being wiped out in 2012.

SQL Server - Error Rebuilding Indexes in Online Mode

In transactional Databases it is a must to have an index rebuilding job running frequently. Fragmentation of indexes is the major cause for any database driven system to perform slower or to crash. As modern database driven application's (Specially Web) are operated 24/7, it demands us to do this operation while the indexes are online.

But SQL Server does not support this operation if you have any LOB data type (text, ntext, image, varchar(max), nvarchar(max), varbinary(max) or xml) column in your indexes. And this is the part of the error message your getting from SQL Server IDE;

"Online index operation cannot be performed for index 'cndx_PrimaryKey_Account' because the index contains column 'Description' of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max) or xml. For non-clustered index the column could be an include column of the index,..."
Blog Directory - OnToplist.com