What is ASP.NET

ASP.NET is a Web application framework developed by Microsoft to build dynamic data driven Web applications and Web services. using HTML, CSS and JavaScript. Moreover, it is a technology for developing, deploying, and running Web applications. ASP.NET is a part of the Microsoft .NET Framework, so all .NET Framework features are available to ASP.NET applications. That means, when you developing ASP.NET applications you have access to classes in the .NET Framework because .NET framework is a collection of classes and ASP.NET is the successor to classic ASP (Active Server Pages).

ASP.NET introduces entirely a new object-oriented execution model. In order to overcome the limitations of ASP (Active Server Pages) , Microsoft has developed a new technology called Microsoft ASP.NET 

ASP.NET is built on the Common Language Runtime (CLR), allowing programmers to write ASP.NET code using any supported .NET languages like VB.NET and C#. Unlike the ASP runtime, ASP.NET uses the Common Language Runtime (CLR) provided by the .NET Framework. The Microsoft .NET Platform provides all of the tools and technologies that are needed to build distributed Web applications. ASP.NET is integrated with Visual Studio .NET, which provides a GUI designer, a rich toolbox, and a fully integrated debugger. In ASP.NET, you can write the HTML code in the .aspx file and the code for programming logic in the code-behind file (.aspx.vb or .aspx.cs ). Also ASP.NET introduces two sets of controls, the HTML controls and the Web controls, which are collectively known as "server controls."

What is a Web Application?
A web application is an application that is accessed by users using a web browser. Examples of web browsers include 
1. Microsoft Internet Explorer
2. Google Chrome
3. Mozilla FireFox
4. Apple Safari
5. Netscape Navigator

Built in string function LEFT, RIGHT, CHARINDEX and SUBSTRING,Replicate, Space, Patindex, Replace and Stuff in sql server

Let us assume we have table user as shown below. 

LEFT(Character_Expression, Integer_Expression) - Returns the specified number of characters from the left hand side of the given string.
forExample:

select left(FirstName,3)FirstName,left(LastName,3)LastName,left (Email,3)Email,left(cast(DOB as date),4)DOB from Users

Output:

RIGHT(Character_Expression, Integer_Expression) - Returns the specified number of characters from the right hand side of the given character expression.
forExample:

select right(FirstName,3)FirstName,right(LastName,3)LastName,right(Email,3)Email,right(cast(DOB as date),2)DOB from Users
Output:

CHARINDEX('Expression_To_Find', 'Expression_To_Search', 'Start_Location') - Returns the starting position of the specified expression in a character string. Start_Location parameter is optional.
forExample:
select charindex('@',Email,1)Indexof@ from Users where ID=1


Output=4


SUBSTRING('Expression', 'Start', 'Length') - As the name, suggests, this function returns substring (part of the string), from the given expression. You specify the starting location using the 'start' parameter and the number of characters in the substring using 'Length' parameter. All the 3 parameters are mandatory.
forExample:

select substring(FirstName,1,1)FirstName,substring(LastName,1,3)LastName,substring(Email,1,CHARINDEX('@',Email,1)-1)Email,left(cast(DOB as date),4)DOB  from Users

Output:

REPLICATE(String_To_Be_Replicated, Number_Of_Times_To_Replicate) - Repeats the given string, for the specified number of times.

forExample:

Select FirstName, LastName, SUBSTRING(Email, 1, 2) + REPLICATE('*',5) + SUBSTRING(Email, CHARINDEX('@',Email), LEN(Email) - CHARINDEX('@',Email)+1) as Email from Users

Output:

REPLACE(String_Expression, Pattern , Replacement_Value)

Replaces all occurrences of a specified string value with another string value.

forExample:

Select Email, REPLICATE(Email, '.com', '.net') as ConvertedEmail from Users

Output:

SPACE(Number_Of_Spaces) - Returns number of spaces, specified by the Number_Of_Spaces argument.

forExample:

Select FirstName + SPACE(5) + LastName as FullName 

from Users

 where ID=1

Output:

PATINDEX('%Pattern%', Expression)

Returns the starting position of the first occurrence of a pattern in a specified expression. It takes two arguments, the pattern to be searched and the expression. PATINDEX() is simial to CHARINDEX(). With CHARINDEX() we cannot use wildcards, where as PATINDEX() provides this capability. If the specified pattern is not found, PATINDEX() returns ZERO.

forExample:

Select 

Email, PATINDEX('%@gmail.com', Email) as FirstOccurence 

from Users 

where PATINDEX('%@gmail.com', Email) >4

Output:

STUFF(Original_Expression, Start, Length, Replacement_expression)

STUFF() function inserts Replacement_expression, at the start position specified, along with removing the charactes specified using Length parameter.

forExample:

Select 

FirstName, LastName,Email, STUFF(Email, 2, 3, '*****') as StuffedEmail 

from Users

 where ID in (1,2,3)

Output:

Built in string functions in sql server

In SQL server, Function can be broadly divided into 2 categoris 1. Built-in functions 2. User Defined functions

Here we will look at the most common string functions available .There are several built-in functions..in sql server.

ASCII(Character_Expression) - Returns the ASCII code of the given character expression.
To find the ASCII Code of capital letter 'A'
Example: Select ASCII('A')
Output: 65

CHAR(Integer_Expression) - Converts an int ASCII code to a character. The Integer_Expression, should be between 0 and 255.

for example: 

Declare @Number int

Set @Number = 1
While(@Number <= 1255)
Begin
select CHAR(@Number)
Set @Number = @Number + 1
End
output:

A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

Printing uppercase alphabets using CHAR() function:
Declare @Number int
Set @Number = 65
While(@Number <= 90)
Begin
 Print CHAR(@Number)
 Set @Number = @Number + 1
End

Printing lowercase alphabets using CHAR() function:
Declare @Number int
Set @Number = 97
While(@Number <= 122)
Begin
 Print CHAR(@Number)
 Set @Number = @Number + 1
End


Another way of printing lower case alphabets using CHAR() and LOWER() functions.
Declare @Number int
Set @Number = 65
While(@Number <= 90)
Begin
 Print LOWER(CHAR(@Number))
 Set @Number = @Number + 1
End

LTRIM(Character_Expression) - Removes blanks on the left handside of the given character expression.

Example: Removing the 3 white spaces on the left hand side of the '   Hello' string using LTRIM() function.
Select LTRIM('   Hello')
Output: Hello

RTRIM(Character_Expression) - Removes blanks on the right hand side of the given character expression.

Example: Removing the 3 white spaces on the left hand side of the 'Hello   ' string using RTRIM() function.
Select RTRIM('Hello   ')
Output: Hello

Example: To remove white spaces on either sides of the given character expression, use LTRIM() and RTRIM() as shown below.
Select LTRIM(RTRIM('   Hello   '))
Output: Hello

LOWER(Character_Expression) - Converts all the characters in the given Character_Expression, to lowercase letters.

Example: Select LOWER('CONVERT This String Into Lower Case')
Output: convert this string into lower case

UPPER(Character_Expression) - Converts all the characters in the given Character_Expression, to uppercase letters.
Example: Select UPPER('CONVERT This String Into upper Case')
Output: CONVERT THIS STRING INTO UPPER CASE

REVERSE('Any_String_Expression') - Reverses all the characters in the given string expression.
Example: Select REVERSE('ABCDEFGHIJKLMNOPQRSTUVWXYZ')
Output: ZYXWVUTSRQPONMLKJIHGFEDCBA

LEN(String_Expression) - Returns the count of total characters, in the given string expression, excluding the blanks at the end of the expression.

Example: Select LEN('SQL Functions   ')
Output: 13