There are three types of UDF (refer to reference), however I will talk about Scalar UDF  here (Which means that function only return single value, you may refer to reference links below for other types).

UDF is quite useful for sometimes, ok, lets go to the point:

Environment:

1. MS SQL Server 2000 or above.
2. Northwind sample database from Microsoft.

Objective:

• List all products in ONE row according to supplier id.

You can try it without UDF, do post your method if you know how to do that.

Here is the part of Product data:

Northwind – Products

First: We need to create a function to concat all product names into one line for each supplier id.

USE NORTHWIND
go

CREATE FUNCTION dbo.ConcatProductName (@sSupplierID as varchar(5))
RETURNS VARCHAR(1000)
AS
BEGIN

   DECLARE @sProduct as varchar(500)

   SET @sProduct = ''

   SELECT @sProduct = @sProduct + ProductName + ', ' FROM PRODUCTS
   WHERE SupplierID = @sSupplierID
   --	group by ProductName

   SET @sProduct = Left(@sProduct, Len(RTrim(@sProduct)) - 1)

   RETURN @sProduct
END

Second: Run and create the function above.

Third: Run the query below the check the result:

Select dbo.ConcatProductName(SupplierID) as ProductName

, SupplierID

From Products

Group by SupplierID

-- drop function ConcatProductName

Query with UDF

See? It work great.

Refer to references below to know pros & cons, and more details about UDF.

References:

Northwind & pubs sample database from Microsoft.

User Defined Functions in Microsoft SQL Server 2000

User Defined Functions

Keywords: Customize function in MSSQL

If you install Visual Studio 2005, you would not face this problem, because the commond prompt and other tools already appear in your classical Windows Start Menu.

However if you install Visual Studio Express Edition, you would not get any shortcuts for those tools, let say you need the command prompt which support Visual Studio .Net, you need to type “cmd” in ‘Run’ dialog, and change the directory to .NetFramework folder.

Unfortunately I using Express edition in office, therefore I need to work around to get the ‘Visual Studio Command Prompt’. After compare with ‘real’ Visual Studio command prompt, below is my work around, the key is:

1. Call a new console screen from batch file.
2. Attach necessary path for .Net in new console screen, so you do not need to change to .Net directory when u need the command in .Net.

Please refer to your own .Net Framework version when doing this, example below is just for .Net version 2.0 .

Steps:

1. Create an empty batch file, insert line in # 2 into batch file, DO include double quote.
2. cmd /K “path=%path%;C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727&&cls”
3. Place the batch file in appropriate directory, create a shortcut for the batch file.
4. Select Properties of the shortcut, change ‘Start in:‘ from current directory to ‘C:\(arbitrary directory)’
5. Now double click the shortcut, Windows prompt up a clean cmd console screen which support .Net Framework command (since already setup in PATH).

 

Explaination for step # 2:

• Command string after /K will be carry out and apply to new console process.
• the ‘&&’ is the separator for seperated commands in same line, the line above consists two commands, first is append the path, second is clear the screen after append path.

 

Explanation for steps # 3 and # 4:

• There are two ways to set the PROMPT in console window, the first way already show in steps 3 & 4, so you just put any startup directory you like in step 4.
• The second way, you need to amend step 2 a bit, change it to below:

cmd /K “path=%path%;C:\WINDOWS\Microsoft.NET\Framework\

v2.0.50727&&PROMPT=C:\Windows$G&&cls”

Ya, it is just add in another command for new console screen (Set the PROMPT=C:\Windows$G).

 

Keyword: Visual Studio Console Window, Tool, Tools, IDE, Command prompt

Still remember my previous post ? Display forward slash (’/’) in date under VB6

I face the same issue in C#, however the solution this time is slightly different with solution in VB6.

//
//   First solution : surround the desired date seperator
//   ('/' in this case) with single quote.
//
string date = DateTime.Today.Date.ToString("dd'/'M'/'yyyy");

//
//   Second solution : same like solution in VB6,
//   except add '@' in front of the string.
//
string date = DateTime.Today.Date.ToString(@"dd\/MM\/yyyy");

Reference: http://authors.aspalliance.com/aspxtreme/sys/demos/datetimeformats.aspx

Keyword: Date seperator, date format, date pattern, C#, .Net, forward slash

Google change new favicon.

Found this when I using gmail just now.

The first favicon start May/30/2008, and the second is today.

When want to show forward slash (‘/’) in date, I use the code below:

Format(Now,"dd/MM/yyyy")

However above code will return 31-01-2008, this is because forward slash “/” will replace by default separator in local PC if default date seperator is “-”.

To check your default date seperator, go to : Control Panel->Regional Settings->Customize->Date-> “Date seperator” in Short date format

You would not see the problem if your local PC date seperator is “/”.

In order to ensure system print the forward slash in date,use the code below:

Format(Now,"dd\/MM\/yyyy")

The code will give 31/01/2008 no matter what is the date seperator.

Reference: http://support.microsoft.com/kb/102045

Search Keyword: Date, slash, seperator

Use the code below:

If rs.BOF And rs.EOF Then  ' Todo - No record found

I always forget this simple code to check empty record in ADO, now write it here to remind myself.

Search Keyword: ADO,check empty record, return empty record, No record found

今天请病假，但是还要在家里做工，显。。。
无所谓，趁机来这里 update 一下，
以上牢骚以中文书写，包同事看不懂，看得懂的也无所谓。

Last time I receive a list of mobile number in .csv file, I need to insert those mobile numbers into temporary table in SQL Server.

The data in .csv file looks like below:

60121234567
60122345678
60133456789
60164567890
60195678901
.
.
.
60140011223

There are few ways to do that, one of the method is using DTS (Or SSIS in MSSQL 2005) to transfer data. However I need to login to Remote Desktop (RD), transfer the .csv file to remote server to do that.

And that day the only two slots for RD already used by my colleagues. So I need to figure out other way.

Ok, I was thinking this way:

1. First, since I can connect to remote SQL Server from my local PC, so I can create a temp table from my PC using create table script.
2. After I got temp table, I can run the INSERT INTO [TEMP_TABLE] (mobile) VALUES (‘601XXXXXXXX’) statement from local PC.
3. Duplicate the insert statement in # 2 for all mobile numbers, and run it from SQL Query.
4. Now I will have a temp table with all mobile numbers inside, no need DTS or SSIS.

Step 1 is not an issue for me, but when come to step 2, I need to append the word INSERT INTO [TEMP_TABLE](mobile) VALUES (‘<mobile-number-in-each-row>‘) to every rows in the .csv file.

So the best way is to replace mobile numbers in each row with the BOLD words above, ok, replace … , replace … , replace function in Notepad++ (Editor I using now), then I press CTRL + H after open Notepad++.

Ehh~~?!, why the Notepad++ search box has one option to pick the search mode for “Regular Expression”? Arrrhh~~~~, I knew it, maybe the Notepad++ author already foresee the situation I facing now.

That is great, I make a quick search on http://notepad-plus.sourceforge.net/uk/regExpList.php, then give a try to my .csv file, haha, it work now.

Description:

1. Open Notepad++, then press CTRL + H.
2. Key in “(601[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9])” in “Find What” textbox.
3. Key in “INSERT INTO [0_TEMP] VALUES(‘\1′)” in “Replace With” textbox.
4. Choose “Regular Expression” in “Search Mode”.
5. Click “Replace All” button.
6. Done, yeah.

Below is how the search box should look like (may vary on different version):

Regular expression replace feature in Notepad++

The final outcome will look like this:

INSERT INTO [0_TEMP] VALUES(‘60121234567′)
INSERT INTO [0_TEMP] VALUES(‘60122345678′)
INSERT INTO [0_TEMP] VALUES(‘60133456789′)
INSERT INTO [0_TEMP] VALUES(‘60164567890′)
INSERT INTO [0_TEMP] VALUES(‘60195678901′)
.
.
.
INSERT INTO [0_TEMP] VALUES(‘60140011223′)

Just a brief explanation to the regexp pattern above (easy for me for future reference):

1. The pattern I using now only support by Notepad++, it is not global regexp pattern.
2. Pattern enclose in “(” and “)” is consider as single parameter that going to be replace later.
3. Notepad++ can recognize the target parameter by using \1, \2, \3 until \9. In my case I only use “\1″.

== [ End ] ==

Words after end: If notepad++ has the features: 1) Column edit, and 2) Hex Edit, then I will vote Notepad++ as the best editor, currently I will say UltraEdit is the best although the interface is qute ugly.

Search Keyword: Tips, Tricks, Notepad++, Tools,  Regular expression, regexp, text editor.

昨晚 （1/12） 8：20pm 我要出门时，发觉天空有一个笑脸，觉得很新奇，就叫妹妹来看，看了一阵我就去跆拳道馆了。

后来姐姐上网找到资料，原来昨晚是一个特别的夜晚，不是经常有这样的奇景的。

借用我朋友 blog 里的资料，嘻嘻。http://auyongck.blogspot.com/2008/12/moon-is-smiling-tonight.html

其他图片参考：

http://akifstation.blogspot.com/2008/12/smiling-night-sky.html

http://www.flickr.com/photos/bunch_of_photons/3075183897/

http://www.flickr.com/photos/prateek_pk/3075742218/

http://www.flickr.com/photos/findonsa/3076124014/

Search Keyword: Smiley night sky, smiling night sky, smile, sky

Malaysia mobile no is in format 601XXXXXXXX (You can find more details from http://www.skmm.gov.my/)

I start learning regular rexpression when I want to do mobile no. validation, basically just refer to two pages, Regular Expression CheatSheet and Regular Expression Tester, a very useful resources to me.

Below is my RegExp test code for mobileNo validation:

^6?01\d{8}$

Let go through the code (Refer to Regular Expression CheatSheet):

1. This reg. exp. only accept format in (601AAAAAAAA) or (01AAAAAAAA), A is digit from 0 ~ 9.
2. ‘^601′ indicates that mobile number must start with ‘601′.
3. Since I want to allow mobile no. in format (01AAAAAAAA), so add a ‘?’ after ‘^6′, it become like this ‘^6?01′. ‘?’ means there is no matching of previous expression or maximum one matching of previous expression, ‘6′ is the one.
4. In (601AAAAAAAA), notice that A x 8, so the regexp follow by ‘\d{8}’, ‘\d’ match any decimal digit, ‘{8}’ says that any eight decimal digit after that will be match. So the test code is ‘^6?01\d{8}’.
5. Yeah the story end. ’^6?01\d{8}’ will be the final code for my validation.
6. After some time, I found the number ‘60191234567999999′ can pass my validation, oops~~, need to fix the checking.
7. After browses some regexp example, ‘$’ is found to prevent above error (previously I do not know what is that ‘$’ serve for).
8. The ‘$’ will make sure ONLY eight decimal digit in the mobile no., so now ‘60191234567999999′ wouldn’t pass my test since there are 13 digits after ‘6019′, but ’60191234567′ will pass the test.
9. Ok, story end again.

Anybody has better test code are welcome.

Reference:

1. http://regexlib.com/CheatSheet.aspx
2. http://regexlib.com/RETester.aspx
3. http://regexlib.com/DisplayPatterns.aspx (Regexp examples)

I did a webpage before to store some message. The point is I need to count the total characters, including new line character.

In IE and Opera new line character will consume 2 spaces because they use the characters “\r\n”, however if the message is saved under Firefox 2 or Firefox 3, the new line character only consume 1 space, this will create a problem because the next program reading those message need the new line characters in format “\r\n”, means at least 2 spaces for that.

After some test I found that Firefox will use a single character “\n” to represent a new line character, it is different with IE and Opera which using “\r\n” as new line character.

This findings shows that message saved under firefox will has a little different because of that “\n”. In order to sync the saved output, I add some extra code to handle this situation for Firefox browser only:

// Handle Firefox save data issue
// (Firefox only use line-feed as new line)

if (browser == "Netscape") {
msg = msg.replace(/\n/g,"\r\n");
}

Above code will detect if there is a Firefox browser, and do some trick on new line character, replace “\n” to “\r\n” when save the message. So that the output will looks same in IE and Opera browser, and has no issue for next program to read.

I never test for Google Chrome and Safari, anybody who interest on that can test at your own. Do let me know your test result,

~~~ * ~~~ * ~~~

Update 14/Oct/2009

Thanks for yair comment. Basically this post is talking about line feed inside <textarea>, if you would like to show a line feed in website, <br /> would be your choice.