Tech Sharing Blog

Trim is a good features when you try to delete the space at the left most, right most or both the left most and right most of the string. The following trim function is ultilize the javascript Regular expression for the trim function

Left Trim

function ltrim(str){

    return str.replace(/^\s+/, ”);

}

Right Trim

function rtrim(str) {

    return str.replace(/\s+$/, ”);

}

All trim

function alltrim(str) {

    return str.replace(/^\s+|\s+$/g, ”);

}

Popularity: 1% [?]

I try to google, call, msn my friend to find out the solution when I stay back in my office until late night yesterday. But still no luck after few hours of trying.

Thinking of gave up when back to office this morning, but one of my friend suddently send me the solution via MSN. That’s the best present I received by today. I’m not sure how many people will looking for this kind of solution, but any way, will post here to share with all and you may take it if you need it.

Basically the idea is that you put all your select, update or delete statment into the variable and use the execute command to execute the whole variable.

My problem:

I have a formula store in a table which is A+B*C+D. My A = 1, B=2, C=3, D=4.

I managed to replace all my variable with the integer using cursor (This maybe not a good solution, but this is not the main discussion in this post)

The finally answer I get is 1+2*3+4. But the bad thing is in the varchar datatype. I only manage to disply 1+2*3+4 but not the answer of 11.

The solution as below:

Declare @MathFomula Varchar(100)

Set @MathFormula = ’1+2*3+4′

Exec(‘SELECT ‘ + @MathFormula  + ‘ AS Result’

Output: 11

* Remember that * and / will calculate at first before + and -

Popularity: 2% [?]

I believe most of the advance computer user will know how to ultilize the window run command. But for the normal user may not ultilized it but more prefer to click by mouse.

The most common use command is [cmd] and also [notepad]. By using the window run command, you may just press on [Window Key] + [R] on your keyboard and type the command.

The following are some of the window run command that you may or may not use, but I suggest if possible try to remember the most common use which will help you to save your time and also reduce the movement of your hand.

Accessibility Controls
access.cpl

Add Hardware Wizard
hdwwiz.cpl

Add/Remove Programs
appwiz.cpl

Administrative Tools
control admintools

Automatic Updates
wuaucpl.cpl

Bluetooth Transfer Wizard
fsquirt

Calculator
calc

Certificate Manager
certmgr.msc

Character Map
charmap

Check Disk Utility
chkdsk

Clipboard Viewer
clipbrd

Command Prompt
cmd

Component Services
dcomcnfg

Computer Management
compmgmt.msc

Date and Time Properties
timedate.cpl

DDE Shares
ddeshare

Device Manager
devmgmt.msc

Direct X Control Panel (If Installed)*
directx.cpl

Direct X Troubleshooter
dxdiag

Disk Cleanup Utility
cleanmgr

Disk Defragment
dfrg.msc

Disk Management
diskmgmt.msc

[More]

Popularity: 1% [?]

I just found out this usefu function in MSSQL when I try to Google for the solution to replace the null value with something else. So that I no need to do the checking again in my application. The function is call

NULLIF and ISNULL

This 2 functions are actually work opposite with each other.

NULLIF(ColumnName, ValueToCompare) accept 2 parameters, and will return the NULL value if both the expression match.

IFNULL(ColumnName, NewValue) accept 2 parameters, the first parameters is the string that you wish you check with, and the 2nd parameter that you wish to replace with.

Popularity: 3% [?]

In SQL, basically there are two ways to Insert data into a table; one is insert it one row per time, another way is insert multiple rows at a times.

For the first method (insert one row per time) is the most common way to insert data into database that we will use normally, and the sample sql syntax as below:

Insert into table1 (col1, col2, col3)
values (value1, value2, value3)

The other insert method that we seldom use, but it may be useful if you like to copy and insert more then one row of record into a table at a time. You may include the Select statement to specify the data that you want to insert into the tables.

The SQL syntax as below:

INSERT INTO table1(col1, col2, cols3)

SELECT value1, value2, value3
FROM table2
WHERE value4 = 1998

There is another way of writing the same SQL:

Select value1, value2, value3

Into table1

From table2

WHERE value4 = 1998

Popularity: 1% [?]

Normally to get the number of row for a particular table, where we will do is as the following query:

select count(*) from Table_name

Yes, you may get your result very fast if your table is very small, but if your table is more then 100k rows, then you may face some delay on that. What the above query do is actually it run the full table scan to get the row count. The scaning can be Logically or Physically. Logically mean that the SQL server only scan from the catch memory, whereas physically scan mean that it really scan through your table.

Actually there is anther alternative way to get the total row count for a table. You can ultilize the sysindexes system table for this purpose.

There is ROWS column in the sysindexes table. This column contains the total row count for each table in your database. So, you can use the following select statement instead of above one:

SELECT rows
FROM sysindexes
WHERE id = OBJECT_ID(‘table_name’) AND indid < 2

There are physical read and logical read operations. A logical read occurs if the page is currently in the cache. If the page is not currently in the cache, a physical read is performed to read the page into the cache. To see how many logical or physical read operations were made, you can use SET STATISTICS IO ON command.

The example:

SET STATISTICS IO ON

GO

SELECT count(*) FROM tbTest

GO

SELECT rows FROM sysindexes WHERE id = OBJECT_ID(‘tbTest’) AND indid < 2

GO

SET STATISTICS IO OFF

GO

---

Result:

———–

10000

(1 row(s) affected)

Table ‘tbTest’. Scan count 1, logical reads 32, physical reads 0, read-ahead reads 0.

rows

———–

10000

(1 row(s) affected)

Table ‘sysindexes’. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.

Popularity: 1% [?]