UserDefined Function to Trim Zeros from decimal value

Most of the times for reporting purpose we want dont want to show extra zeros that comes along with decimal values. To my surprise SQL Server doesnt provide any function which will help trimming extra useless zeros from decimal. After searching a lot about this I found that if you want you have to write your own function which will serve this purpose for you.
And I found one scalar function which you can call in your procedures to trim extra zeros from a decimal value. Pass the decimal value, variable or column you want to remove the extra zeros from to the function, and it returns a trimmed string:


CREATE FUNCTION [dbo].[fnTrimZeros]
(
@decValue decimal(7,5)
)
RETURNS varchar(9)
AS
BEGIN
DECLARE @txtTrimmed varchar(9), @chrTest varchar(1)

SELECT @txtTrimmed = Convert(varchar(8),@decValue)
SELECT @chrTest = Substring(@txtTrimmed,Len(@txtTrimmed),1)
WHILE @chrTest = '0'
BEGIN
SELECT @txtTrimmed = substring(@txtTrimmed,0,len(@txtTrimmed))
SELECT @chrTest = substring(@txtTrimmed,len(@txtTrimmed),1)
END
IF @chrTest = '.' -- remove unnecessary decimal point
SELECT @txtTrimmed = substring(@txtTrimmed,0,len(@txtTrimmed))

RETURN @txtTrimmed + '%' -- optional % sign formatting

END



Call the function by:

SELECT dbo.fnTrimZeros(@DecimalVariable) AS TrimmedDecimal

-or-

SELECT dbo.fnTrimZeros(DecimalColumn) AS TrimmedDecimal FROM dbo.DecimalTable

You can adjust the precision of the decimals, or length of the varchars to suit your needs. Also, if you convert the trimmed string back to a decimal, you're probably just going to add extra zeros back on since you can't predict the precision of the decimal on the fly...
Ideally these kind of string manipulation should be done on the presentation layer (when possible, 'cause sometimes it's not!) instead of doing this in back end.

Rarely Used but Useful SQL Function "NULLIF"

We all are very well aware and familiar with the function IFNULL, which is used to check if the value is NULL and provide replacement value for NULL.

Recently I came across one more useful SQL function "NULLIF". This works exactly opposite to IFNULL. In here you can find for some specific value and can replace it with NULL.
Now you will wonder why we may need to replace some useful value with NULL. Well I have one situation for you. Suppose you are doing some mathematical operations and you have to account for division by zero. Instead of writing multiple checks and IF clauses, NULLIF function comes in handy.

One of the simplest solutions is to do something like this:

SELECT @v1 / NULLIF( @v2, 0)

This will return NULL if division was invalid (Division by zero) or else
it will give the proper result.

If you prefer the result of an invalid division to be zero, you can add
a COALESCE:

SELECT COALESCE( @v1 / NULLIF(@v2,0), 0)


Hope that you will also find this function useful. Feel free to put your comments.

Potential of 'SixthSense' Technology by Pranav Mistry

Guys, I was amazed to see an Indian who came up with such an important technology that will actually change our physical world. The guy I am walking about is Pranav Mistry from MIT India and I must say that he has got amazing vision.
This video is a must watch for every Techy who want to make a difference.

At TEDIndia, Pranav Mistry demos several tools that help the physical world interact with the world of data -- including a deep look at his SixthSense device and a new, paradigm-shifting paper "laptop".
In an onstage Q&A, Mistry says he'll open-source the software behind SixthSense, to open its possibilities to all.

http://economictimes.indiatimes.com/tv/TED-India-Pranav-Mistry/videoshow_ted/5231080.cms


Hope you like it..!!!