SQL Server String Functions

String functions are mainly used to change the case of strings,concatenate strings,reverse strings,extract various part of strings and perform many other types of string manipulation.

In SQL Server there is a Several built-in string functions to perform string manipulations.All below functions takes string input value and return a string or numeric value.

ASCII : Returns the ASCII code value of a character(leftmost character of string).

Syntax: ASCII(character)
SELECT ASCII('a')->97
SELECT ASCII('A')->65
SELECT ASCII('1')->49
SELECT ASCII('ABC')->65
For Upper character 'A' to 'Z' ASCII value 65 to 90
For Lower character 'A' to 'Z' ASCII value 97 to 122
For digit '0' to '9' ASCII value 48 to 57

UNICODE : UNICODE function works just like ASCII function,except returns Unicode standard integer value. UNICODE could be useful if you are working with international character sets.

Syntax: UNICODE(character)
SELECT UNICODE('F')->70
SELECT UNICODE('STRING FUNCTION')->83 (leftmost character of string)

LOWER : Convert character strings data into lowercase.

Syntax: LOWER(string)
SELECT LOWER('STRING FUNCTION')->string function

UPPER : Convert character strings data into Uppercase.

Syntax: UPPER(string)
SELECT UPPER('string function')->STRING FUNCTION

LEN : Returns the length of the character string.

Syntax: LEN(string)
SELECT LEN('STRING FUNCTION')->15

REPLACE : Replaces all occurrences of the second string(string2) in the first string(string1) with a third string(string3).

Syntax: REPLACE('string1','string2','string3')
SELECT REPLACE('STRING FUNCTION','STRING','SQL')->SQL Function
Returns NULL if any one of the arguments is NULL.

LEFT : Returns left part of a string with the specified number of characters counting from left.LEFT function is used to retrieve portions of the string.

Syntax: LEFT(string,integer)
SELECT LEFT('STRING FUNCTION', 6)->STRING

RIGHT : Returns right part of a string with the specified number of characters counting from right.RIGHT function is used to retrieve portions of the string.

Syntax: RIGHT(string,integer)
SELECT RIGHT('STRING FUNCTION', 8)->FUNCTION

LTRIM : Returns a string after removing leading blanks on Left side.(Remove left side space or blanks)

Syntax: LTRIM(string)
SELECT LTRIM('   STRING FUNCTION')->STRING FUNCTION

RTRIM : Returns a string after removing leading blanks on Right side.(Remove right side space or blanks)

Syntax: RTRIM( string )
SELECT RTRIM('STRING FUNCTION   ')->STRING FUNCTION

REVERSE : Returns reverse of a input string.

Syntax: REVERSE(string)
SELECT REVERSE('STRING FUNCTION')->NOITCNUF GNIRTS

REPLICATE : Repeats a input string for a specified number of times.

Syntax: REPLICATE (string, integer)
SELECT REPLICATE('FUNCTION', 3)->FUNCTIONFUNCTIONFUNCTION

SPACE : Returns a string of repeated spaces.The SPACE function is an equivalent of using REPLICATE function to repeat spaces.

Syntax: SPACE ( integer) (If integer is negative,a null string is returned.)
SELECT ('STRING') + SPACE(1) + ('FUNCTION')->STRING FUNCTION

SUBSTRING : Returns part of a given string.

SUBSTRING function retrieves a portion of the given string starting at the specified character(startindex) to the number of characters specified(length).

Syntax: SUBSTRING (string,startindex,length)
SELECT SUBSTRING('STRING FUNCTION', 1, 6)->STRING
SELECT SUBSTRING('STRING FUNCTION', 8, 8)->FUNCTION

STUFF : Deletes a specified length of characters and inserts another set of characters at a specified starting point.

STUFF function is useful to inserts a set of characters(string2) into a given string(string1) at a given position.

Syntax: STUFF (string1,startindex,length,string2)
SELECT STUFF('STRING FUNCTION', 1, 6, 'SQL')->SQL FUNCTION
SELECT STUFF('SQL FUNCTION', 5, 8, 'Tutorial')->SQL Tutorial

CHARINDEX : Returns the starting position of the specified string(string1) in a character string(string2).

Syntax: CHARINDEX (string1,string2 [,start_location ])
SELECT CHARINDEX('SQL','Useful SQL String Function')->8
SELECT CHARINDEX('SQL','Useful SQL String Function')->19
If string1 is not found within string2,CHARINDEX returns 0.

PATINDEX : PATINDEX function works very similar to CHARINDEX function.PATINDEX function returns the starting position of the first occurrence of a pattern in a specified string, or zeros if the pttern is not found.

Using PATINDEX function you can search pattern in given string using Wildcard characters(%).The % character must come before and after pattern.

Syntax: PATINDEX('%pattern%',string)
SELECT PATINDEX('%SQL%','Useful SQL String Function')->8
SELECT PATINDEX('Useful%','Useful SQL String Function')->1
SELECT PATINDEX('%Function','Useful SQL String Function')->19
If pattern is not found within given string,PATINDEX returns 0.

Cross Page Posting in ASP.NET 2.0

Introduction

Almost every web application inputs data from the user and process it, in ASP.NET this process is called page posting. By default in ASP.NET 1.1 page can only post back to itself which some times is not the functionality developers wants. Although it’s possible in ASP.NET 1.1 that one page post backs to other page but this is quite expensive and not the recommended approach. Microsoft realize the importance of cross page posting in gives this functionality in ASP.NET 2.0 along with other features, so in this article I will first discuss the cross page inASP.NET1.1 and then elaborate this concept in ASP.NET 2.0.

Cross Page Postback in ASP.NET 1.1

Asp.net 1.1 developers can achieve the functionality of cross page posting through Server.Transfer Method which preserver the HttpContext of current page before transferring to other page, because HttpContext is preserved you can access the source page’s items collection in target hence called cross page posting.

This functionality comes at price, the basic problem is that this transfer occurs at server level and current page must postback to itself before it can transfer to other page results extra processing overhead.

Security is another problem with the Server.Transfer method along with the viewstate issues; you can find more on MSDN and its knowledge base articles so let’s concentrate on ASP.NET 2.0.

Cross Page postback in ASP.NET 2.0

System.Web.UI.WebControls.IButtonControl interface contains a new property called PostBackUrl which points to the page to which the current page will postback, Button, ImageButton and LinkButton implements this interface and exposes the cross page postback functionality.

When user clicks the button the current page will postback to the specified page which can access the source page controls through Page.PreviousPage property which returns the reference of previous page, once got the reference of previous page you can use the FindControl method to get the reference of particular or you can expose public properties from source page to provide the type safe access i.e

Login.aspx

<asp:Textbox ID="TextBoxUserName" Runat="server" />

<asp:Textbox ID="TextBoxPassword" Runat="server" />

<asp:Button ID="ButtonLogin" Runat="server" Text="Login"

         PostBackUrl="UserAuthenticate.aspx" />

Expose the following properties in code behind file 

public TextBox UserName

{

    get

    {

        return TextBoxUserName;

    }

}

public TextBox Password

{

    get

    {

        return TextBoxPassword;

    }

}

UserAuthenticate.aspx

<%@ PreviousPageType VirtualPath="~/Login.aspx" %>

<script runat="server">   

protected void Page_Load(object sender, System.EventArgs e)

{

    String username = PreviousPage.UserName.Text;

    String password = PreviousPage.Password.Text;

    // Authenticate username/password

}

PreviousPageType directive specifies the virtual path of the source page for strongly typed access to the source page, but the problem with this approach is that you can only specify one previous page type and it cannot be used for pages which can be destination for multiple pages in this case only option left is late bind access using FindControl method or reflection.

It is not necessary that target page always executes as a postback, it can also be execute as stand alone, to determine this Page class contains new property called IsCrossPagePostBack it returns true when page is executing as a result of cross page postback, this behaves exactly same as IsPostBack property. So let’s modify the previous code to incorporate this

if (IsCrossPagePostBack)

{

    String username = PreviousPage.UserName.Text;

    String password = PreviousPage.Password.Text;

    // Authenticate username/password

}

Display Database Image using RS 2008

Select the Reporting Services image component from the Toolbox and drag and drop into an emptytablix column.

Following are store image in binary form:

This action will trigger the Reporting Services Image Properties Wizard.
You can later alter the properties of the image cell by a click on the image properties menu item on the context menu of the image cell.

Set the image properties as follows :
1) Set the image source as database, since we are selecting the image in binary format from database using reporting services dataset.
2) When database is chosen as the source of the image, you will select the field name as [file] field of the dataset in the Use this field dropdown list.
3) Also as the last step select the MIME type as image/jpeg (or which is approtiate for your case)

Resize Database Image and Size Properties of the SQL Database Image

You can also set the size properties of the database image and image component within the tablixcomponent by displaying the image properties and navigating to the Size tab.
There you can select one of the following size properties for display database images.
Original size
Fit to size
Fit proportional
Clip

Repeat Header / Keep Header Visible in Tables in RS 2008

If a report with a table is created using the wizard feature (In Solution Explorer, right click on Reports --> Add New Report) Table Headers will repeat on every page.

Instead if a report is created without using the wizard (In Solution Explorer, right click on Reports --> Add --> New Item) Table Headers will not repeat on every page. Setting Repeat Header (Rows/Columns) on each Page for the Tablix will not help either

if you are using a "table"-style layout:

1. in the grouping pane, click on the small triangle and select "Advanced Mode" to show static members:
   
    
2. verify that you have the properties grid turned on (in BI Development Studio use F4, in Report Builder go to the "View" tab and select "Properties")
    
3. select the corresponding (static) item in the row group hierarchy
    
4. in the properties grid:
   - set KeepWithGroup to After
   - set RepeatOnNewPage to true for repeating headers
   - set FixedData to true for keeping headers visible

Difference between Temp Table and Table Variable in Sql

Biggest Difference between Temp Table and Table Variable are as follow:

As we studies they are include performance, Storage in memory and use tempdb to store etc..

Table variable are transaction neutral. And thus aren’t bound in transaction.

     Temp table behave as normal tables and are bound by transaction.
    
      A Simple Example are as follow:

We see that the table variable still exists and has all it's data unlike the temporary table that doesn't exists when the transaction rollbacked.

·