Monday, June 14, 2010

Encrypt column data in SQL server

CREATE FUNCTION [dbo].[Encrypt] ( @InputString VARCHAR(4000) )

RETURNS NVARCHAR(4000) WITH ENCRYPTION AS

BEGIN

DECLARE @vEncryptedString NVARCHAR(4000)

DECLARE @vIdx INT

DECLARE @vBaseIncrement INT

SET @vIdx = 1

SET @vBaseIncrement = 128

SET @vEncryptedString = ''''

WHILE @vIdx <= LEN(@InputString)

BEGIN

SET @vEncryptedString = @vEncryptedString +

NCHAR(ASCII(SUBSTRING(@InputString, @vIdx, 1)) +

@vBaseIncrement + @vIdx - 1)

SET @vIdx = @vIdx + 1

END

RETURN @vEncryptedString

END

To decrypt check following


Split string/varchar in SQL server

Create function [dbo].[Split](@String nvarchar (4000),@Delimiter nvarchar (10))

returns @ValueTable table ([Value] nvarchar(4000))

begin

declare @NextString nvarchar(4000)

declare @Pos int

declare @NextPos int

declare @CommaCheck nvarchar(1)

--Initialize

set @NextString = ''

set @CommaCheck = right(@String,1)

--Check for trailing Comma, if not exists, INSERT

if (@CommaCheck <> @Delimiter )

begin

set @String = @String + @Delimiter

end

--Get position of first Comma

set @Pos = charindex(@Delimiter,@String)

set @NextPos = 1

--Loop while there is still a comma in the String of levels

while (@pos <> 0)

begin

set @NextString = substring(@String,1,@Pos - 1)

insert into @ValueTable ( [Value]) Values (@NextString)

set @String = substring(@String,@pos +1,len(@String))

set @NextPos = @Pos

set @pos = charindex(@Delimiter,@String)

end

return

END

Get current database name in SQL server

Get current database name in SQL server.


SELECT DB_NAME()

Monday, June 7, 2010

Calculate gunning fog index in .Net





"Gunning fog" index is designed to measure the readability of English writing paragraphs. The result shows normally an estimate of the "number of years" of education he/she needs to understand the text on a first reading the text paragraph.



So here is the formula ...

 0.4\left( \left(\frac{\mbox{words}}{\mbox{sentence}}\right) + 100\left(\frac{\mbox{complex words}}{\mbox{words}}\right) \right )


Here is sample code to calculate gunning fog index written in VB.Net

//some variables
Dim inputText As String = txtInput.Text
Dim ltr As String
Dim wordCount As Integer
Dim sentenceCount As Integer
Dim syllableCount As Integer
Dim complexCount As Integer
Dim i As Integer
Dim inWord As Boolean = False
Dim vowels As String = "aeiouy"
TextBox1.Text = ""

' First make the input string case insensitive.
inputText = inputText.ToLower()
For i = 0 To inputText.Length() - 1
ltr = inputText.Substring(i, 1).ToLower
If ltr = "'" Then
   ' do nothing in case of apostrophe
ElseIf (ltr >= "a" And ltr <= "z") Then ' check for alphabetic input
If Not inWord Then ' new word
wordCount += 1 ' so add 1 to count
End If
inWord = True
Else ' could be space, full stop, comma etc
inWord = False ' no longer in word
' check if end of sentence
If (ltr = "." Or ltr = "?" Or ltr = "!" Or ltr = ":" Or ltr = ";") Then
sentenceCount += 1 ' add 1 to count
End If
End If
Next
Dim input As String = inputText.Length() - 1
Dim ch As Char
Dim inVowel As Boolean = False
' syllable count is zero if no input
If (input <>Then
syllableCount = 0
complexCount = 0
End If
' e at end of word doesn't count as a vowel
ch = inputText.Substring(inputText.Length - 1)
If (ch = "e") Then
input -= 1
End If
'For Each ch In inputText
' If (vowels.IndexOf(ch) >= 0) Then
' If Not inVowel Then
' syllableCount += 1
' End If
' inVowel = True
' End If
'Next
Dim word As String
Dim pieces As String()
'Replace all what is not needed here
inputText = inputText.Replace(".", " ")
inputText = inputText.Replace(":", " ")
inputText = inputText.Replace(";", " ")
inputText = inputText.Replace("?", " ")
inputText = inputText.Replace("!", " ")
inputText = inputText.Replace(",", "")
inputText = inputText.Replace("'", " ")
inputText = inputText.Replace(" ", " ")
'Now make pieces
pieces = inputText.Split(" ")
syllableCount = 0
complexCount = 0
For index As Integer = 0 To pieces.Length - 1
word = pieces(index)
word = word.ToLower().Trim()
Dim pattern As String = "[aeiouy]+"
'Handle vowels
Dim count As Integer = Regex.Matches(word, pattern).Count
If word.EndsWith("e") Then
count -= 1
End If
'some special cases
If word.EndsWith("cial") Or word.EndsWith("tia") Or word.EndsWith("cius") Or word.EndsWith("cious") Or word.EndsWith("giu") Or word.EndsWith("ion") Or word.EndsWith("iou") Then 'Or word.EndsWith("sia") Or word.EndsWith("ely") Then
count = 1
End If
syllableCount += count
If count <>Then
count = 1
End If
If count >= 3 Then
complexCount += 1
End If
Next
'Now display all results on GUI
TextBox1.Text += "Words: " & wordCount.ToString() & Environment.NewLine
TextBox1.Text += "Sentences: " & sentenceCount.ToString() & Environment.NewLine
TextBox1.Text += "Syllable: " & syllableCount.ToString() & Environment.NewLine
TextBox1.Text += "Complex Words: " & complexCount.ToString() & Environment.NewLine
TextBox1.Text += "Fog index: " & (0.4 * ((wordCount / sentenceCount) + (100 * (complexCount / wordCount)))).ToString() & Environment.NewLine
TextBox1.Text += "Flesch-Kincaid Reading Age: " & (206.876 - (1.015 * (wordCount / sentenceCount)) - (84.6 * (syllableCount / wordCount))).ToString() & Environment.NewLine

Thursday, June 3, 2010

Ad Hoc Reporting Tools

Reporting tools are applications that are used to analyze, report and present data in a better way. This may run as stand alone tool and can be integrated with any system.

Here are few ad hoc reporting tools that i have found amazing. These provide easy integration with your data and wizard base approach to build reports at design time. You are not supposed to be a professional software or database engineer to use these tools, but with a normal knowledge of your system and database you may handle it comfortably.

Izenda



-------------------------------------------------------------
-------------------------------------------------------------

DBxtra







If you are looking for some open source solution, please take a look





Reporting Tools (Open Source)

Reporting tools are applications that are used to analyze, report and present data in a better way. This may run as stand alone tool and can be integrated with any system.

Here are few open source reporting tools...

BIRT (Business Intelligence Reporting Tools)

Pentaho

OpenReports

DataVision

Jasper



Tuesday, June 1, 2010

SQL Prompt (Redgate) Alternatives

SQL Prompt is a great tool for intellisense in SQL environment while working with queries, it enables the development environment to show tables schema and other information in a quick and effective format.


Here are few alternatives for this...

SQL Buddy: An open source tool that helps user to work with query management with SQL server.


SoftTree SQL Assistant: A powerful tool that provides intellisense in many databases i.e Oracle, SQL Server, DB2, Sybase, MS Access, PostgreSQL, and MySQL. Also it provides API for developers for integration.


Technical specifications:
Disk space - 11.2 MB, Memory - 128 MB, CPU - Pentium or compatible
OS - Windows 2000/XP/2003/Vista/2008/7
Supported database systems:
  • Oracle 8i, 9i, 10g, 11g
  • Microsoft SQL Server 2000, 2005, 2008
  • DB2 UDB 7, 8, 9, 9.5
  • DB2 UDB for iSeries
  • Sybase ASE 12.x, 15.x
  • Sybase ASA 7, 8, 9, 10
  • PostgreSQL 8.x
  • MySQL 5.x
  • Microsoft Access 2003, 2007
Supported database connections: MySQL connector,OCI, SMO, ODBC, ADO.NET