LPAD in T-SQL and Crystal Report Formula

I am sure there should have some neater ways to do this but if all you need are just working version, here it goes.

T-SQL

REPLICATE('0', 8 - LEN(LTRIM(STR(YourTableField)))) + LTRIM(STR(YourTableField))


Crystal Report Formula

StringVar Message := "";
StringVar Num := {YourCrystalReportTable.Field};
Num := Trim (Num);
NumberVar Counter := 8 - Length(Num) ;
While (Counter > 0) do
(
Message := Message & "0";
Counter := Counter - 1;
);
Message := Message & Num

Comments

Post a Comment

Popular posts from this blog

Clearcase check in/uncheck out all the checked out files script

I am so used to source safe recursive check in and found troublesome in clearcase having to do it manually one by one.

This is the Unix script that I have come out with

//for undo check out all the checked out files
cleartool lsco -cview -avobs | cleartool unco -rm `awk '{ print $(5) }' | sed -e 's/^\"*//' -e 's/ *\"$//'`
//for check in all the checked out files
cleartool lsco -cview -avobs | cleartool ci -nc `awk '{ print $(5) }' | sed -e 's/^\"*//' -e 's/ *\"$//'`


//Using xargs

cleartool lsco -cview -avobs -short | xargs cleartool ci
Read more

Duplicating a local copy of project from TFS and making web software client factory to work

My team members were asking me about how come their files can't be modified and etc even if it is a duplicated local copy. I tried it out and realized it is indeed not working as told. My finding was that the TFS has a tight control on the versioning of the file, even if the files are offline or those files are duplicate copies of the original files, the TFS control still present in every aspects of the actions you want to do onto those files.

So, it seems like there need to have an official declaration to inform Visual Studio that the project files are out of the TFS radar. After some internet searches and trials, it seems like to "permanently" detach project file from TFS are not so straightforward and not properly documented too. It requires some kind of hacking seems.

Below are the steps required after the files get copied out from the TFS.

1. Change all the files from readonly to non-readonly.
2. Remove your local workspace from the Visual Studio.
Go to menu it…
Read more

MSSQL GROUP_CONCAT

This is using XML feature of the SQL server. (nothing new here)


select Type, RestaurantNames from Restaurant AS A CROSS APPLY
(SELECT RestaurantName + ',' FROM Restaurant AS B WHERE A.Type = B.Type FOR XML PATH('')) D (RestaurantNames) GROUP BY Type, RestaurantNames


This is to get something like below (similar to MYSQL GROUP_CONCAT)


Type |RestaurantNames
----- ---------------
Chinese Food | Ah Yat Abalone, Liang Yah Yong Tau Foo,
Indian Food | Kanna Curry House,
Western Fast Food | Burger King, McDonald

instead of multiple rows.

like
Type |RestaurantName
----- ---------------
Chinese Food |Ah Yat Abalone
Chinese Food |Liang Yah Yong Tau Foo
Indian Food |Kanna Curry House
Read more