April 24, 2011

Convert Number To Words in SQL Sever

Ref: http://www.novicksoftware.com/udfofweek/Vol2/T-SQL-UDF-Vol-2-Num-9-udf_Num_ToWords.htm


SET QUOTED_IDENTIFIER ON


SET ANSI_NULLS ON

SET NOCOUNT ON

GO

CREATE FUNCTION dbo.NumToWords (



@Number Numeric (38, 0) -- Input number with as many as 18 digits



) RETURNS VARCHAR(8000)

AS BEGIN

DECLARE @inputNumber VARCHAR(38)

DECLARE @NumbersTable TABLE (number CHAR(2), word VARCHAR(10))

DECLARE @outputString VARCHAR(8000)

DECLARE @length INT

DECLARE @counter INT

DECLARE @loops INT

DECLARE @position INT

DECLARE @chunk CHAR(3) -- for chunks of 3 numbers

DECLARE @tensones CHAR(2)

DECLARE @hundreds CHAR(1)

DECLARE @tens CHAR(1)

DECLARE @ones CHAR(1)

IF @Number = 0 Return 'Zero'

-- initialize the variables

SELECT @inputNumber = CONVERT(varchar(38), @Number)

, @outputString = ''

, @counter = 1

SELECT @length = LEN(@inputNumber)

, @position = LEN(@inputNumber) - 2

, @loops = LEN(@inputNumber)/3

-- make sure there is an extra loop added for the remaining numbers

IF LEN(@inputNumber) % 3 <> 0 SET @loops = @loops + 1

-- insert data for the numbers and words

INSERT INTO @NumbersTable SELECT '00', ''

UNION ALL SELECT '01', 'one' UNION ALL SELECT '02', 'two'

UNION ALL SELECT '03', 'three' UNION ALL SELECT '04', 'four'

UNION ALL SELECT '05', 'five' UNION ALL SELECT '06', 'six'

UNION ALL SELECT '07', 'seven' UNION ALL SELECT '08', 'eight'

UNION ALL SELECT '09', 'nine' UNION ALL SELECT '10', 'ten'

UNION ALL SELECT '11', 'eleven' UNION ALL SELECT '12', 'twelve'

UNION ALL SELECT '13', 'thirteen' UNION ALL SELECT '14', 'fourteen'

UNION ALL SELECT '15', 'fifteen' UNION ALL SELECT '16', 'sixteen'

UNION ALL SELECT '17', 'seventeen' UNION ALL SELECT '18', 'eighteen'

UNION ALL SELECT '19', 'nineteen' UNION ALL SELECT '20', 'twenty'

UNION ALL SELECT '30', 'thirty' UNION ALL SELECT '40', 'forty'

UNION ALL SELECT '50', 'fifty' UNION ALL SELECT '60', 'sixty'

UNION ALL SELECT '70', 'seventy' UNION ALL SELECT '80', 'eighty'

UNION ALL SELECT '90', 'ninety'

WHILE @counter <= @loops BEGIN

-- get chunks of 3 numbers at a time, padded with leading zeros

SET @chunk = RIGHT('000' + SUBSTRING(@inputNumber, @position, 3), 3)

IF @chunk <> '000' BEGIN

SELECT @tensones = SUBSTRING(@chunk, 2, 2)

, @hundreds = SUBSTRING(@chunk, 1, 1)

, @tens = SUBSTRING(@chunk, 2, 1)

, @ones = SUBSTRING(@chunk, 3, 1)

-- If twenty or less, use the word directly from @NumbersTable

IF CONVERT(INT, @tensones) <= 20 OR @Ones='0' BEGIN

SET @outputString = (SELECT word

FROM @NumbersTable

WHERE @tensones = number)

+ CASE @counter WHEN 1 THEN '' -- No name

WHEN 2 THEN ' thousand ' WHEN 3 THEN ' million '

WHEN 4 THEN ' billion ' WHEN 5 THEN ' trillion '

WHEN 6 THEN ' quadrillion ' WHEN 7 THEN ' quintillion '

WHEN 8 THEN ' sextillion ' WHEN 9 THEN ' septillion '

WHEN 10 THEN ' octillion ' WHEN 11 THEN ' nonillion '

WHEN 12 THEN ' decillion ' WHEN 13 THEN ' undecillion '

ELSE '' END

+ @outputString

END

ELSE BEGIN -- break down the ones and the tens separately

SET @outputString = ' '

+ (SELECT word

FROM @NumbersTable

WHERE @tens + '0' = number)

+ '-'

+ (SELECT word

FROM @NumbersTable

WHERE '0'+ @ones = number)

+ CASE @counter WHEN 1 THEN '' -- No name

WHEN 2 THEN ' thousand ' WHEN 3 THEN ' million '

WHEN 4 THEN ' billion ' WHEN 5 THEN ' trillion '

WHEN 6 THEN ' quadrillion ' WHEN 7 THEN ' quintillion '

WHEN 8 THEN ' sextillion ' WHEN 9 THEN ' septillion '

WHEN 10 THEN ' octillion ' WHEN 11 THEN ' nonillion '

WHEN 12 THEN ' decillion ' WHEN 13 THEN ' undecillion '

ELSE '' END

+ @outputString

END

-- now get the hundreds

IF @hundreds <> '0' BEGIN

SET @outputString = (SELECT word

FROM @NumbersTable

WHERE '0' + @hundreds = number)

+ ' hundred '

+ @outputString

END

END

SELECT @counter = @counter + 1

, @position = @position - 3

END

-- Remove any double spaces

SET @outputString = LTRIM(RTRIM(REPLACE(@outputString, ' ', ' ')))

SET @outputstring = UPPER(LEFT(@outputstring, 1)) + SUBSTRING(@outputstring, 2, 8000)

RETURN @outputString -- return the result

END

GO

GRANT EXEC on dbo.NumToWords TO PUBLIC

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

April 18, 2011

Disable Copy/Paste in a Textbox

<asp:TextBox ID="TextBox1" TextMode="password" oncopy="return false;" oncut="return false;" onpaste="return false;" runat="server"></asp:TextBox>

April 03, 2011

Visual Studio shortcut keys

http://www.dofactory.com/ShortCutKeys/ShortCutKeys.aspx

1. CTRL + ".": This is actually a shortcut for a shortcut. it is the same as CTRL + SHIFT + F10 which opens the smart tag window and allows you to add Using statements


2. CTRL + "k" + "f" and CRTL + "k" + "d": these two will format the code in the window to be nicely indented. using "d" will format all the document while using "f" will format only selected text. The formatting is for all types of documents, HTML, Xaml, XML, C#… This one is my favorite.

3. SHIFT + Del: This one will cut the entire row from the document and past it to the clipboard. No need to select the row, just put the marker there and click SHIFT + Del and it is gone.

4. CTRL + "k" + "c" and CTRL + "k" + "u": These two are for commenting selected text (the "c" options) and uncommenting selected text (the "u" option).

5. ALT + ENTER: this little shortcut will open up the Properties window

6. F12: I think you all know this but still F12 is the shortcut for the "Go to definition" command which will take you to the definition of the object your marker is currently on.

7. F9: Another one i think you all know, clicking on F9 will add a breakpoint to the code line your marker is currently at. Clicking F9 again will remove this breakpoint from that line.

8. CTRL + ALT + "q": This one will open the Quick watch window while you debug