Age Calculating or Date Subtraction in SQL

// Posted By Suresh

declare @BirthDate datetime,@ToDate datetime, @result datetime
set @BirthDate = '2/2/1984'
set @ToDate = GETDATE()
declare @BirthDay int, @ToDay int, @BirthMonth int, @ToMonth int, @BirthYear int, @ToYear int, @PreToMonth int, @BorrowDay int
declare @Day int, @Month int, @Year int

select @BirthDay= DATEPART(day,@BirthDate),@BirthMonth= DATEPART(MONTH,@BirthDate), @BirthYear = DATEPART(year,@BirthDate)
select @ToDay= DATEPART(day,@ToDate),@ToMonth= DATEPART(MONTH,@ToDate), @ToYear = DATEPART(year,@ToDate)

if(@ToMonth >1)
begin
SET @PreToMonth = @ToMonth - 1;
end
else
begin
set @PreToMonth = 12;
end

if(@BirthDay<=@ToDay)
begin
set @Day= @ToDay - @BirthDay
end
else
begin
if(@PreToMonth = 1 or @PreToMonth = 3 or @PreToMonth = 5 or @PreToMonth = 7 or @PreToMonth = 8 or @PreToMonth = 10 or @PreToMonth = 12)
begin
set @BorrowDay = 31
end
else if(@PreToMonth = 4 or @PreToMonth = 6 or @PreToMonth = 9 or @PreToMonth = 11)
begin
set @BorrowDay = 30
end
else
begin
if(@ToYear%400 =0 or (@ToYear%100 <> 0 AND @ToYear%4 = 0))
begin
set @BorrowDay = 29
end
else
begin
set @BorrowDay = 28
end
end
SET @ToMonth = @ToMonth - 1
set @ToDay = @ToDay + @BorrowDay
set @Day= @ToDay - @BirthDay
end


if(@BirthMonth<=@ToMonth)
begin
set @Month= @ToMonth - @BirthMonth
end
else
begin
set @ToYear = @ToYear - 1
set @ToMonth = @ToMonth + 12
set @Month = @ToMonth - @BirthMonth
end

if(@BirthYear<=@ToYear)
begin
set @Year= @ToYear - @BirthYear
end
else
begin
set @Year = 0
end

print cast(@Year as varchar(4)) + ' Year, '+ cast(@Month as varchar(4)) + ' Month, ' + cast(@Day as varchar(4)) + ' Days'

Get column names of a specified table in SQL

//Post By Suresh Chand

SELECT [NAME] AS 'Columns' FROM SYSCOLUMNS WHERE [ID] = Object_Id('Table Name')

OR

exec sp_columns 'Table Name'

String Functions in C#

Swaps the cases in a string
//Suresh -> sURESH, SURESH -> suresh, SuReSh -> sUrEsH
public string SwapCases(string sInput)
{
string sRetValue = "";
for (int i = 0; i < sInput.Length; i++)
{
if (string.Compare(sInput.Substring(i, 1), sInput.Substring(i, 1).ToUpper(), false) == 0)
sRetValue += sInput.Substring(i, 1).ToLower();
else
sRetValue += sInput.Substring(i, 1).ToUpper();
}
return sRetValue;
}


Alternates cases between letters of a string, letting the user pick if the first letter is capitalized
public string AlternateCases(string sInput, bool startWithUpper)
{
string sRetValue = "";
for (int i = 0; i < sInput.Length; i++)
{
if (startWithUpper)
sRetValue += sInput.Substring(i, 1).ToUpper();
else
sRetValue += sInput.Substring(i, 1).ToLower();
startWithUpper = !startWithUpper;
}
return sRetValue;
}

Removes vowels from a word
//Suresh-> Srsh
public string RemoveVowels(string sInput)
{
string sRetValue = "";
string Letter;
for (int i = 0; i < sInput.Length; i++)
{
Letter = sInput.Substring(i, 1);
if (string.Compare(Letter, "a", true) != 0 && string.Compare(Letter, "e", true) != 0 && string.Compare(Letter, "i", true) != 0 && string.Compare(Letter, "o", true) != 0 && string.Compare(Letter, "u", true) != 0)
{
sRetValue += Letter;
}
}
return sRetValue;
}

Keep vowels from a word
//Suresh -> ue
public string KeepVowels(string sInput)
{
string sRetValue = "";
string Letter;
for (int i = 0; i < sInput.Length; i++)
{
Letter = sInput.Substring(i, 1);
if (string.Compare(Letter, "a", true) == 0 || string.Compare(Letter, "e", true) == 0 || string.Compare(Letter, "i", true) == 0 || string.Compare(Letter, "o", true) == 0 || string.Compare(Letter, "u", true) == 0)
{
sRetValue += Letter;
}
}
return sRetValue;
}

Returns an array converted into a string
public string ArrayToString(Array sInput, string separator)
{
string sRetValue = "";
for (int i = 0; i < sInput.Length; i++)
{
sRetValue += sInput.GetValue(i).ToString();
if (i != sInput.Length - 1)
sRetValue += separator;
}
return sRetValue;
}

Inserts a separator after every letter
//Suresh,- -> S-u-r-e-s-h
public string InsertSeparator(string sInput, string separator)
{
string sRetValue = "";
for (int i = 0; i < sInput.Length; i++)
{
sRetValue += sInput.Substring(i, 1);
if (i != sInput.Length - 1)
sRetValue += separator;
}
return sRetValue;
}

Inserts a separator after every Count letters
//Suresh, -, 2 -> Su-re-sh
public string InsertSeparatorEvery(string sInput, string separator, int count)
{
string sRetValue = "";
for (int i = 0; i < sInput.Length; i+=count)
{
if (i + count < sInput.Length)
sRetValue += sInput.Substring(i, count);
else
sRetValue += sInput.Substring(i);
if (i < sInput.Length - count)
sRetValue += separator;
}
return sRetValue;
}

Reverses a string
//Suresh -> hseruS
public string Reverse(string sInput)
{
string sRetValue = "";
for (int i = sInput.Length - 1; i >= 0; i--)
{
sRetValue += sInput.Substring(i, 1);
}
return sRetValue;
}



Capitalizes a word or sentence
//suresh -> Suresh, my self suresh jangid -> My Self Suresh Jangid
public string Capitalize(string sInput)
{
if (sInput.Length == 0) return "";
if (sInput.Length == 1) return sInput.ToUpper();
return sInput.Substring(0, 1).ToUpper() + sInput.Substring(1);
}

Checks whether a word or sentence is capitalized
//Suresh -> True, My self suresh jangid -> True
public bool IsCapitalized(string sInput)
{
if (sInput.Length == 0) return false;
return string.Compare(sInput.Substring(0, 1), sInput.Substring(0, 1).ToUpper(), false) == 0;
}

Checks whether a string is in all lower case
//suresh -> True, Suresh -> False
public bool IsLowerCase(string sInput)
{
for (int i = 0; i < sInput.Length; i++)
{
if (string.Compare(sInput.Substring(i, 1), sInput.Substring(i, 1).ToLower(), false) != 0)
return false;
}
return true;
}

Checks whether a string is in all upper case
//suresh -> False, Suresh -> False, SURESH -> True
public bool IsUpperCase(string sInput)
{
for (int i = 0; i < sInput.Length; i++)
{
if (string.Compare(sInput.Substring(i, 1), sInput.Substring(i, 1).ToUpper(), false) != 0)
return false;
}
return true;
}

Alternates cases between letters of a string, first letter's case stays the same
//Bi -> Bi, suresh -> SuReSh
public string AlternateCases(string sInput)
{
if (sInput.Length == 0) return "";
if (sInput.Length == 1) return sInput; //Cannot automatically alternate
bool firstIsUpper = string.Compare(sInput.Substring(0, 1), sInput.Substring(0, 1).ToUpper(), false) != 0;
string sRetValue = sInput.Substring(0, 1);
for (int i = 1; i < sInput.Length; i++)
{
if (firstIsUpper)
sRetValue += sInput.Substring(i, 1).ToUpper();
else
sRetValue += sInput.Substring(i, 1).ToLower();
firstIsUpper = !firstIsUpper;
}
return sRetValue;
}

Checks to see if a string has alternate cases
//sUrEsH -> True
public bool IsAlternateCases(string sInput)
{
if (sInput.Length <= 1) return false;

bool lastIsUpper = string.Compare(sInput.Substring(0, 1), sInput.Substring(0, 1).ToUpper(), false) == 0;

for (int i = 1; i < sInput.Length; i++)
{
if (lastIsUpper)
{
if (string.Compare(sInput.Substring(i, 1), sInput.Substring(i, 1).ToLower(), false) != 0)
return false;
}
else
{
if (string.Compare(sInput.Substring(i, 1), sInput.Substring(i, 1).ToUpper(), false) != 0)
return false;
}

lastIsUpper = !lastIsUpper;
}

return true;
}

Counts total number of a char or chars in a string
//Suresh, s -> 2, Suresh, re -> 1
public int CountTotal(string sInput, string chars, bool ignoreCases)
{
int count = 0;
for (int i = 0; i < sInput.Length; i++)
{
if (!(i + chars.Length > sInput.Length) && string.Compare(sInput.Substring(i, chars.Length), chars, ignoreCases) == 0)
{
count++;
}
}
return count;
}

Checks to see if a string contains vowels
//Suresh -> True, Srsh -> False
public bool HasVowels(string sInput)
{
string currentLetter;
for (int i = 0; i < sInput.Length; i++)
{
currentLetter = sInput.Substring(i, 1);

if (string.Compare(currentLetter, "a", true) == 0 ||
string.Compare(currentLetter, "e", true) == 0 ||
string.Compare(currentLetter, "i", true) == 0 ||
string.Compare(currentLetter, "o", true) == 0 ||
string.Compare(currentLetter, "u", true) == 0)
{
//A vowel found
return true;
}
}

return false;
}

Checks if string is nothing but spaces
//" " -> True
public bool IsSpaces(string sInput)
{
if (sInput.Length == 0) return false;
return sInput.Replace(" ", "").Length == 0;
}

Checks if the string has all the same letter/number
//aaaaaa -> true, aaaaaaaab -> false
public bool IsRepeatedChar(string sInput)
{
if (sInput.Length == 0) return false;
return sInput.Replace(sInput.Substring(0, 1), "").Length == 0;
}

Checks if string has only numbers
//12453 -> True, 234d3 -> False
public bool IsNumeric(string sInput)
{
for (int i = 0; i < sInput.Length; i++)
{
if (!(Convert.ToInt32(sInput[i]) >= 48 && Convert.ToInt32(sInput[i]) <= 57))
{
//Not integer value
return false;
}
}
return true;
}

Checks if the string contains numbers
//suresh -> False, sure65sh -> True
public bool HasNumbers(string sInput)
{
return System.Text.RegularExpressions.Regex.IsMatch(sInput, "\\d+");
}

Checks if string is numbers and letters
//suresh34 -> True, $chool! -> False
public bool IsAlphaNumberic(string sInput)
{
char currentLetter;
for (int i = 0; i < sInput.Length; i++)
{
currentLetter = sInput[i];

if (!(Convert.ToInt32(currentLetter) >= 48 && Convert.ToInt32(currentLetter) <= 57) &&
!(Convert.ToInt32(currentLetter) >= 65 && Convert.ToInt32(currentLetter) <= 90) &&
!(Convert.ToInt32(currentLetter) >= 97 && Convert.ToInt32(currentLetter) <= 122))
{
//Not a number or a letter
return false;
}
}
return true;
}

Checks if a string contains only letters
//Hi -> True, Hi123 -> False
public bool isLetters(string sInput)
{
char currentLetter;
for (int i = 0; i < sInput.Length; i++)
{
currentLetter = sInput[i];

if (!(Convert.ToInt32(currentLetter) >= 65 && Convert.ToInt32(currentLetter) <= 90) &&
!(Convert.ToInt32(currentLetter) >= 97 && Convert.ToInt32(currentLetter) <= 122))
{
//Not a letter
return false;
}
}
return true;
}

Returns the initials of a name or sentence
//capitalize - whether to make intials capitals
//includeSpace - to return intials separated (True - S. J. or False - S.J.)
//Suresh Jangid -> S. J. or S.J.
public string GetInitials(string sInput, bool capitalize, bool includeSpace)
{
string[] words = sInput.Split(new char[] { ' ' });

for (int i = 0; i < words.Length; i++)
{
if (words[i].Length > 0)
if (capitalize)
words[i] = words[i].Substring(0, 1).ToUpper() + ".";
else
words[i] = words[i].Substring(0, 1) + ".";
}

if (includeSpace)
return string.Join(" ", words);
else
return string.Join("", words);
}

Capitalizes the first letter of every word
//the earth -> The Earth
public string GetTitle(string sInput)
{
string[] words = sInput.Split(new char[] { ' ' });

for (int i = 0; i < words.Length; i++)
{
if (words[i].Length > 0)
words[i] = words[i].Substring(0, 1).ToUpper() + words[i].Substring(1);
}

return string.Join(" ", words);
}


Checks whether the first letter of each word is capitalized
//The Earth -> True, the earth -> False
public bool IsTitle(string sInput)
{
string[] words = sInput.Split(new char[] { ' ' });

for (int i = 0; i < words.Length; i++)
{
if (words[i].Length > 0)
if (string.Compare(words[i].Substring(0, 1).ToUpper(), words[i].Substring(0, 1), false) != 0)
return false;
}
return true;
}

Returns all the locations of a char in a string
//SURESH, s -> 0, 4
public int[] IndexOfAll(string sInput, string chars)
{
List indices = new List();
for (int i = 0; i < sInput.Length; i++)
{
if (sInput.Substring(i, 1) == chars)
indices.Add(i);
}

if (indices.Count == 0)
indices.Add(-1);

return indices.ToArray();
}


Gets the char in a string at a given position, but counting from right to left
//Suresh, 2 -> e
public char CharRight(string sInput, int index)
{
if (sInput.Length - index - 1 >= sInput.Length ||
sInput.Length - index - 1 < 0)
return new char();

string str = sInput.Substring(sInput.Length - index - 1, 1);
return str[0];
}

Gets the char in a string from a starting position plus the index
//suresh, 3, 1 -> s
public char CharMid(string sInput, int startingIndex, int countIndex)
{
if (startingIndex + countIndex < sInput.Length)
{
string str = sInput.Substring(startingIndex + countIndex, 1);
return str[0];
}
else
return new char();
}

Function that works the same way as the default Substring, but it takes Start and End (exclusive) parameters instead of Start and Length
//Suresh, 1, 3 -> ur
public string SubstringEnd(string sInput, int start, int end)
{
if (start > end) //Flip the values
{
start ^= end;
end = start ^ end;
start ^= end;
}

if (end > sInput.Length) end = sInput.Length; //avoid errors

return sInput.Substring(start, end - start);

}

Splits strings, but leaves anything within quotes
//(Has issues with nested quotes
//This is a "Suresh jangid" ->
//This
//is
//a
//Suresh Jangid
public string[] SplitQuotes(string sInput, bool ignoreQuotes, string separator)
{
if (ignoreQuotes)
return sInput.Split(separator.ToCharArray());
else
{
string[] words = sInput.Split(separator.ToCharArray());
List newWords = new List();

for (int i = 0; i < words.Length; i++)
{
if (words[i].StartsWith('"'.ToString()))
{
List linked = new List();
for (int y = i; y < words.Length; y++)
{
if (words[y].EndsWith('"'.ToString()))
{
linked.Add(words[y].Substring(0, words[y].Length - 1));
i = y;
break;
}
else
{
if (words[y].StartsWith('"'.ToString()))
linked.Add(words[y].Substring(1));
}
}
newWords.Add(string.Join(separator, linked.ToArray()));
linked.Clear();
}
else
newWords.Add(words[i]);
}
return newWords.ToArray();
}
}

Convert an ArrayList to delimited string in c#

//posted By Suresh
//Input = StrA!%StrC!%StrB!%StrD!%StrE";
//Output = StrE!%StrD!%StrC!%StrB!%StrA";

string InStr = "StrA!%StrC!%StrB!%StrD!%StrE";
string outStr = "";
string[] del = new string[] {"!%"};
string[] TempArr = InStr.Split(del, StringSplitOptions.None);
Array.Sort(TempArr);
Array.Reverse(TempArr);
outStr = string.Join(("!%",TempArr);

Custom Pagging and Sorting in GridView

//Posted By Suresh


//HTML Code
<br /><div style="overflow: auto; height: 280px; width: 680px"><br /> <asp:GridView ID="GVBooking" runat="server" BackColor="#FBDAA0" Width="1200px" AutoGenerateColumns="true" BorderColor="Black" BorderWidth="1px" CellPadding="0" Font-Names="Arial" BorderStyle="Solid" ForeColor="Black" Font-Size="9pt" OnRowCreated="GVBooking_RowCreated" EmptyDataText="Data Not Available" AllowPaging="True" PageSize="15" OnPageIndexChanging="GVBooking_PageIndexChanging" Font-Strikeout="False" OnDataBound="GVBooking_DataBound" OnSorting = "GVBooking_Sorting" AllowSorting="true"><br /> <PagerTemplate><br /> <asp:ImageButton ID="ImgbtnFirst" runat="server" ImageUrl="~/Images/first.png" CommandArgument="First" CommandName="Page" OnCommand="Paginate" Height="18px" ToolTip="First" /><br /> <asp:ImageButton ID="ImgbtnPrev" runat="server" ImageUrl="~/Images/previous.png" CommandArgument="Prev" CommandName="Page" OnCommand="Paginate" Height="18px" ToolTip="Previous" /><br /> Page<br /> <asp:DropDownList OnSelectedIndexChanged="ddlPages_SelectedIndexChanged" ID="ddlPages"<br /> runat="server" AutoPostBack="True"><br /> </asp:DropDownList><br /> of<br /> <asp:Label Height="18px" ID="lblPageCount" ToolTip="Total Pages" runat="server"></asp:Label><br /> <asp:ImageButton ID="ImgbtnNext" runat="server" ImageUrl="~/Images/next.png" CommandArgument="Next"<br /> CommandName="Page" OnCommand="Paginate" Height="18px" ToolTip="Next" /><br /> <asp:ImageButton ID="ImgbtnLast" runat="server" ImageUrl="~/Images/last.png" CommandArgument="Last"<br /> CommandName="Page" OnCommand="Paginate" Height="18px" ToolTip="Last" /><br /> </PagerTemplate><br /> <PagerStyle CssClass="GridPager" BackColor="#CC3300" Font-Bold="True" ForeColor="White"<br /> VerticalAlign="Middle" Height="18px" HorizontalAlign="Left" /><br /> <HeaderStyle CssClass="GridHeader"></HeaderStyle><br /> <AlternatingRowStyle CssClass="GridAltItem" /><br /> <PagerSettings Position="Top" /><br /> <RowStyle CssClass="GridItem" /><br /> </asp:GridView><br /> </div><br />
//Code Behind Code

private const string ASCENDING = " ASC";
private const string DESCENDING = " DESC";
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
pupulategrid();
}
}

protected void GVBooking_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
GVBooking.PageIndex = e.NewPageIndex;
pupulategrid();

}

protected void GVBooking_RowCreated(object sender, GridViewRowEventArgs e)
{
// Leave header and footer of Gridview and assign in the rest of the item row
if (e.Row.RowType != DataControlRowType.Header || e.Row.RowType != DataControlRowType.Header)
{
e.Row.Attributes.Add("onmouseout", "this.className='normalRow'");
e.Row.Attributes.Add("onmouseover", "this.className='highlightRow'");
}
}

protected void GVBooking_DataBound(Object sender, EventArgs e)
{
GridViewRow gvrPager = GVBooking.TopPagerRow;
if (gvrPager == null) return;
// get your controls from the gridview
DropDownList ddlPages = (DropDownList)gvrPager.Cells[0].FindControl("ddlPages");
Label lblPageCount = (Label)gvrPager.Cells[0].FindControl("lblPageCount");
if (ddlPages != null)
{
// populate pager
for (int i = 0; i < GVBooking.PageCount; i++)
{
int intPageNumber = i + 1;
System.Web.UI.WebControls.ListItem lstItem = new System.Web.UI.WebControls.ListItem(intPageNumber.ToString());
if (i == GVBooking.PageIndex)
lstItem.Selected = true;
ddlPages.Items.Add(lstItem);
}
}
// populate page count
if (lblPageCount != null)
lblPageCount.Text = GVBooking.PageCount.ToString();
}

protected void ddlPages_SelectedIndexChanged(Object sender, EventArgs e)
{
GridViewRow gvrPager = GVBooking.TopPagerRow;
DropDownList ddlPages = (DropDownList)gvrPager.Cells[0].FindControl("ddlPages");
GVBooking.PageIndex = ddlPages.SelectedIndex;
// a method to populate your grid
pupulategrid();
}

protected void Paginate(object sender, CommandEventArgs e)
{
// get the current page selected
int intCurIndex = GVBooking.PageIndex;
switch (e.CommandArgument.ToString().ToLower())
{
case "first":
GVBooking.PageIndex = 0;
break;
case "prev":
if (GVBooking.PageIndex > 0)
GVBooking.PageIndex = intCurIndex - 1;
break;
case "next":
int i = GVBooking.PageIndex + 1;
if (i <= GVBooking.PageCount)
{
GVBooking.PageIndex = i;
}
break;
case "last":
GVBooking.PageIndex = GVBooking.PageCount;
break;
}
// popultate the gridview control
pupulategrid();
}

public void pupulategrid()
{
//Grid Filling From Data base
//Bind Grid
// Also Maintain Session Of Data table
//Session["DataTableTotalBooking"]=Dt
}




public SortDirection GridViewSortDirection
{
get
{
if (ViewState["sortDirection"] == null)
ViewState["sortDirection"] = SortDirection.Ascending;

return (SortDirection)ViewState["sortDirection"];
}
set { ViewState["sortDirection"] = value; }
}

protected void GVBooking_Sorting(object sender, GridViewSortEventArgs e)
{
string sortExpression = e.SortExpression;

if (GridViewSortDirection == SortDirection.Ascending)
{
GridViewSortDirection = SortDirection.Descending;
SortGridView(sortExpression, DESCENDING);
}
else
{
GridViewSortDirection = SortDirection.Ascending;
SortGridView(sortExpression, ASCENDING);
}

}

private void SortGridView(string sortExpression, string direction)
{

DataTable dt = (DataTable)Session["DataTableTotalBooking"];

DataView dv = new DataView(dt);
dv.Sort = sortExpression + direction;
GVBooking.DataSource = dv;
GVBooking.DataBind();
}

File Compression Or DeCompression in C#

// Posted By Suresh

//Compression
public void Compression()
{
string SourcePath = "c:\\bps.txt";
string DestPath = "c:\\bps.gz";
FileStream sourceFile = File.OpenRead(SourcePath);
FileStream destinationFile = File.Create(DestPath);
byte[] buffer = new byte[sourceFile.Length];
sourceFile.Read(buffer, 0, buffer.Length);
using (GZipStream output = new GZipStream(destinationFile, CompressionMode.Compress))
{
output.Write(buffer, 0, buffer.Length);
}
// Close the files.
sourceFile.Close();
destinationFile.Close();
}


//DeCompression
public void DeCompression()
{
string SourcePath = "c:\\bps.gz";
string DestPath = "c:\\bps1.txt";
FileStream sourceFile = File.OpenRead(SourcePath);
FileStream destinationFile = File.Create(DestPath);
// Because the uncompressed size of the file is unknown,
// we are using an arbitrary buffer size.
byte[] buffer = new byte[10485760]; //10MB
int n;
using (GZipStream input = new GZipStream(sourceFile, CompressionMode.Decompress, false))
{
n = input.Read(buffer, 0, buffer.Length);
destinationFile.Write(buffer, 0, n);
}

// Close the files.
sourceFile.Close();
destinationFile.Close();
}

String Functions in Sql Using Example

// Posted By Suresh


Declare @str varchar(50), @str1 varchar(50)
set @str = 'Suresh'
set @str1 = 'Jangid'


--Convert In Upper case
Select UPPER(@str) -- OUTPUT >> SURESH


--Convert In Lower case
Select LOWER(@str) -- OUTPUT >> suresh


--Add Space Between Two Strings
Select @str + SPACE(1) + @str1 --OUTPUT >> Suresh Jangid


--Reverse From any Sting
Select REVERSE(UPPER(@str)) -- OUTPUT >> HSERUS

Select CHARINDEX('r',@str) -- OUTPUT >> 3


--Char to Ascii
Select ASCII('A') --OUTPUT >> 65


--Character Find Out From String Non Case Sensitive
Select LEN(@str)-LEN(REPLACE(@str,'s',''))-- OUTPUT >> 2


--Character Find Out From String Case Sensitive
Select LEN(@str COLLATE SQL_Latin1_General_Cp1_CS_AS)-LEN(REPLACE(@str COLLATE SQL_Latin1_General_Cp1_CS_AS,'s' COLLATE SQL_Latin1_General_Cp1_CS_AS,''))-- OUTPUT >> 1


--ASCII TO Char Conversion
Select Nchar(65) --OUTPUT >> A


Select UNICODE('A') --OUTPUT >> 65


--Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types
SELECT PATINDEX ( '%s%', @str) --OUTPUT >> 1


--The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position
Select STUFF(@str,2,2,@str1) --OUTPUT >> SJangidesh


--Returns the left part of a character string with the specified number of characters.
Select LEFT(@str,3) --OUTPUT >>Sur


--Returns the Right part of a character string with the specified number of characters
Select RIGHT(@str,3) --OUTPUT >> esh


--Repeats a string value a specified number of times
Select REPLICATE(@str,2)--OUTPUT >> SureshSuresh


--Returns part of a character, binary, text, or image expression
Select SUBSTRING(@str,3,LEN(@str)) --OutPut >> resh


--Returns a character expression after it removes leading blanks From Left
Select LTRIM(' '+@str)


--Returns a character expression after it removes leading blanks From Right
Select RTRIM(@str+' ')


--Replaces all occurrences of a specified string value with another string value.
Select REPLACE(@str,'s','x') -- OutPut >> xurexh

SQL Table to XML conversion using SQL Query

// Posted By Suresh


SELECT *
FROM Emp_Master s
ORDER BY s.Id
FOR XML PATH('Employee'), root('Employees')

Simple Encryption/Decryption Functions in SQL SERVER

// Posted By Suresh

//Encryption
CREATE FUNCTION [dbo].[Suresh_Encryption_UDF] ( @pStr VARCHAR(100) )
RETURNS NVARCHAR(100) WITH ENCRYPTION AS
BEGIN
DECLARE @vEncStr NVARCHAR(100)
DECLARE @vIndex INT
DECLARE @vBase INT
SET @vIndex = 1
SET @vBase = 128
SET @vEncStr = ''
WHILE @vIndex <= LEN(@pStr)
BEGIN
SET @vEncStr = @vEncStr + NCHAR(ASCII(SUBSTRING(@pStr, @vIndex, 1)) + @vBase + @vIndex - 1)
SET @vIndex = @vIndex + 1
END
RETURN @vEncStr
END
GO


//Decryption
CREATE FUNCTION [dbo].[Suresh_Decryption_UDF] ( @pEncStr NVARCHAR(100) )
RETURNS VARCHAR(100) WITH ENCRYPTION AS
BEGIN
DECLARE @vStr VARCHAR(100)
DECLARE @vIndex INT
DECLARE @vBase INT
SET @vIndex = 1
SET @vBase = 128
SET @vStr = ''
WHILE @vIndex <= LEN(@pEncStr)
BEGIN
SET @vStr = @vStr + CHAR(UNICODE(SUBSTRING(@pEncStr, @vIndex, 1)) - @vBase - @vIndex + 1)
SET @vIndex = @vIndex + 1
END
RETURN @vStr
END
GO

Count Character Occurrences in SQL QUERY

//Posted By Suresh


Declare @Str varchar(100), @CStr Varchar(5)

Set @Str = 'Hi, My self Suresh Chand' -- String
Set @CStr = 'S' --Finding Character

-- Not case-sensitive
select (LEN(@Str) - LEN(REPLACE(@Str, @CStr, '')))

-- Case-sensitive
SELECT (LEN(@Str) - LEN(REPLACE(@Str COLLATE SQL_Latin1_General_Cp1_CS_AS, @CStr COLLATE SQL_Latin1_General_Cp1_CS_AS, '')))