Monday, July 7, 2014
Create or Restore SQL Database with backup from different location
There comes a scenario when you want to restore database with another database backup but would like its data and transaction files to be located at different location.

Original Database - OldDB (database whose backup is with you)
New Database - NewDB (new database to be created by restoring backup)

Step 1- get the logical file name for OldDB backup using the following sql


This will give you logical file name of the Data and transaction log files of the backup.

Step 2- Use below sql to create new database using backup from different location

WITH MOVE 'OldDB Backup Data file Logical name' TO 'E:\Dev2008\Data\NewDB_Data.mdf',
MOVE 'OldDB Backup Log file Logical name' TO 'E:\Dev2008\Logs\NewDB_Log.ldf'

Posted On Monday, July 7, 2014 4:35 PM | Comments (0)
Tuesday, March 11, 2014
TFS - Unshelve shelvesets
If you ever come across a scenario where you want to unshelve a shelve set into a branch other than the one it's saved in, then it's an ideal case to use TFS Power tools.

use tfpt unshelve to achieve unshelving of a shelve set to a different branch


/>tfpt unshelve [shelvesetname[;username]] [/nobackup][/migrate /source:serverpath /target:serverpath]

  • shelvesetname          The name of the shelveset to unshelve 
  • /nobackup              Skip the creation of a backup shelveset 
  • /migrate               Rewrite the server paths of the shelved items                          (for example to unshelve into another branch) 
  • /source:serverpath     Source location for path rewrite (supply with /migrate) 
  • /target:serverpath     Target location for path rewrite (supply with /migrate) 
  • /undo                  Undo pending changes from an unshelved shelveset 
  • /batchsize:num         Set the batch size for server calls (default 500)
you saved a shelveset into Dev branch with the title 'Test Shelve set'. Now you wish to unshelve it into PROD branch. Use the below command -

/> tfpt unshelve “Test Shelve set” /nobackup /migrate /source:”$/MyTeamProject/DevBranch” /target:”$/MyTeamProject/ProdBranch”

Posted On Tuesday, March 11, 2014 3:53 PM | Comments (0)
Wednesday, July 24, 2013
Retrieve .Net Control ID in Javascript

If you need to retrieve a client ID of an asp:net control in a javascript function, then you can use the below function -

function $$(id, context) {
        var el = $("#" + id, context);
        if (el.length < 1)
            el = $("[id$=_" + id + "]", context);
        return el;
var tempDotNetControl = 'aspTextTemporary';
var ClientSideID = $$(aspTextTemporary);

Please bear in mind, this function is useful if you want to retrieve client ID of a different DotNet control based on some condition, otherwise if it’s always static then you can just use <%= aspTextTemporary.ClientID %>"

Posted On Wednesday, July 24, 2013 6:22 PM | Comments (0)
Thursday, June 6, 2013
LINQ example with GroupBy clause on a property in object
//group all above policies by broker to send an email about "broker re-assignment'
var brokerPolicies = from pol in policy.ListData.AsEnumerable()
                     group pol by pol.Field<int>("intUserID") into grp
                     select new
                             brokerID = grp.Key,
                             listOfPolicies =  from x in grp.AsEnumerable()
                                               select new 
                                                      list = String.Format("{0:00}-{1:000000}",x.Field<short>("intPolicySequence"), x.Field<int>("intPolicyNumber"))
Posted On Thursday, June 6, 2013 5:55 PM | Comments (0)
Tuesday, March 12, 2013
SQL Server–Find Nth Largest/smallest entry in a table

We could find the maximum and minimum value in a table by using the SQL inbuilt aggregate functions MAX() and MIN() respectively. But, many times there comes a scenario when we need to find the second or third largest/smallest entry in table. There is no built-in SQL function to get this information but we can use Row_Number() function to our advantage to find Nth Largest or smallest value in table.

For example – usually, to find maximum value we normally use the following

select MAX(column_name) as column_name from table_name 

This is equivalent to the following

SELECT ROW_NUMBER() OVER ( ORDER BY column_name DESC) ROW_NUM, column_name FROM table_name
)  T 
WHERE row_num = 1
However, this can then be used to get any largest value in the table, all you have to do is just change the row_num value in the where clause.
So, to get the second largest value set 
WHERE row_num = 2

Similarly to find the second smallest entry, just sort the column by ascending and set the where clause to 2.

Hope this helps!

Posted On Tuesday, March 12, 2013 3:45 PM | Comments (1)
Tuesday, March 5, 2013
Reseed Identity column in SQL Server
Use the below SQL command to reset the seed on the identity column -


The following example forces the current identity value in the AddressTypeID column in the AddressType table to a value of 10. Because the table has existing rows, the next row inserted will use 11 as the value, that is, the new current increment value defined for the column value plus 1.

DBCC CHECKIDENT ("Person.AddressType", RESEED, 10);

Posted On Tuesday, March 5, 2013 5:25 PM | Comments (2)
Monday, March 4, 2013
SQL Server - get last executed SQL statement
We all know the usage of sp_who2 command in SQL server, its life-saver when you need to know what's causing your query take longer time to execute or in other words who's blocking you r query.

But, SQL server has many in-built functions which lets you sneak a peek inside the sql server and we can use them to our advantage. One of them is 


Using this function, we can see the last executed sql statement in that process id.

There are several ways to find out what is the latest run query from system table sys.sysprocesses.

SELECT @sqltext sql_handle
FROM sys.sysprocesses
WHERE spid 61

Posted On Monday, March 4, 2013 3:32 PM | Comments (3)
SQL server - get list of active connections to each database
Many times, we want list of all users who are connected to the SQL server - to determine it  use the below query. It returns the Database, Number of open connections and logged-in user credentials.

SELECT DB_NAME(dbid) as Database, COUNT(dbid) as Number Of Open Connections,
loginame as LoginName
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid, loginame

Hope this helps.
Posted On Monday, March 4, 2013 3:24 PM | Comments (7)
Friday, March 1, 2013
Find out last modification to the SQL table
If you need to find when was the table last updated for insert/delete/update .. use the below query to find it. It will show you the last updated datetime for the table

last_user_update, *

FROM sys.dm_db_index_usage_stats

WHERE database_id = DB_ID( 'DB_Name')
Posted On Friday, March 1, 2013 3:30 PM | Comments (1)
Thursday, February 28, 2013
SQL Server Management Studio crashed - recover unsaved sql query
If for some reasons SQL server instance dies or it crashes and if you had an unsaved query - you could still recover it from the backup files that sql server creates whenever it crashes unexpectedly.

The path for the backup .sql files is something like the following  -

C:\Users\USER_PROFI\Documents\SQL Server Management Studio\Backup Files\Solution1
Posted On Thursday, February 28, 2013 5:31 PM | Comments (6)
DMV to figure out unused and missing Indexes on table in SQL server
MS SQL Server 2005 onwards provides quite a lot of views and functions which can be used to keep a tab on the database engine and also gives you statistics which helps you in making informed decision about the database schemas. 

This are collectively called as DMV (Dynamic management views).

For example - the following DMV can help you show the state of indexes in the database.It shows you the table name, the number of indexes on that table, how many of those indexes have been unused since either the last restart or the index was (re)created, and how many indexes SQL Server thought it would like to have on the table. In a well tuned database, the unused and missing counts would both be 0. If they are, you have exactly the right number of indexes for the workloads hitting your tables.

        COALESCE(Unused.IdxCount0)    AS IDXCOUNT,
        COALESCE(Unused.UnusedCount0) AS UNUSEDCOUNT,
        COALESCE(CONVERT(DECIMAL(6,1),(CONVERT(DECIMAL(10,2),Unused.UnusedCount)/CONVERT(DECIMAL(10,2),Unused.IdxCount))100)0) AS UnusedPct

FROM    sys.objects so

    (   SELECT      s.OBJECT_ID,
                    COUNT(*)        AS idxcount,
                    SUM(CASE WHEN   s.user_seeks    = 0
                                AND s.user_scans    = 0
                                AND s.user_lookups  = 0
                            THEN    1
                            ELSE 0 END) AS UnusedCount

            FROM    sys.dm_db_index_usage_stats s
            JOIN    sys.indexes     i
            ON      s.OBJECT_ID     = i.OBJECT_ID
            AND     s.index_id      = i.index_id
            WHERE   s.database_id   = DB_ID()
            AND     OBJECTPROPERTY(s.OBJECT_ID,‘IsMsShipped’) = 0
            GROUP BY    s.OBJECT_ID
        ) Unused
ON      Unused.OBJECT_ID = so.OBJECT_ID
                    COUNT(*) AS MICOUNT
            FROM    sys.dm_db_missing_index_groups  g
            JOIN    sys.dm_db_missing_index_group_stats s
            ON      s.group_handle                  = g.index_group_handle
            JOIN    sys.dm_db_missing_index_details d
            ON      d.index_handle                  = g.index_handle
            WHERE   d.database_id = DB_ID()
            GROUP BY d.OBJECT_ID
ON      Missing.OBJECT_ID = so.OBJECT_ID
WHERE   so.type_desc = ‘USER_TABLE’
AND     (Missing.MICOUNT > 0
        OR Unused.UnusedCount > 0)


This returns the following in sample database = 


Table_1 1 1 0 100.0

Table_2 1 1 0 100.0

Table_3 1 0 4 0.0

Table_4 1 0 3 0.0

Table_5 1 0 1 0.0

Table_6 1 0 5 0.0

Posted On Thursday, February 28, 2013 3:49 PM | Comments (0)
Some times, you may want the database to interpret the date value as per the format you have provided rather than using the default database level format (which is mostly set to mdy as default US date format). SQL server SET DATEFORMAT syntax comes to rescue in this situations - 

you can set the dateformat to your convenience and then can run the insert or update with your format and SQL will not crib about it.  Please note, this is applicable only for the session and it does not set the date format at the server level. 
Also, the data in the database will get saved as per the format set for the server so there won't be any messing up of data.

A hypothetical scenario could be  - if you receive a CSV file which you need to import into database and all the dates in the CSV are in DD/MM/YYYY format whereas your database accepts date in YYYY/MM/DD format.

CREATE TABLE #temp (datesample smalldatetime)




VALUES ('09/28/2007')



VALUES ('2007/28/09')



VALUES ('2007/08/28')



VALUES ('28/12/2006')

SELECT DateSample

FROM #temp 


This would return the following output -


2007-09-28 00:00:00

2007-09-28 00:00:00

2007-08-28 00:00:00

2006-12-28 00:00:00

Hope this helps someone.


Posted On Thursday, February 28, 2013 3:40 PM | Comments (0)
Wednesday, March 28, 2012
App_Offline.htm, taking site down for maintenance

There is much simpler and graceful way to shut down a site while doing upgrade to avoid the problem of people accessing site in the middle of a content update.


Basically, if you place file with name 'app_offline.htm' with below contents in the root of a web application directory, ASP.NET will shut-down the application,  and stop processing any new incoming requests for that application. 

ASP.NET will also then respond to all requests for dynamic pages in the application by sending back the content of the app_offline.htm file (for example: you might want to have a “site under construction” or “down for maintenance” message).


Then after upgrade, just rename/delete app_offline.htm file…and the site would be back to normal.

Just remember that the size of the file should be greater than 512 bytes, doesn't matter even if you add some comments to it to push the byte size as long as it's of the size greater than 512 - it'll work fine.



<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "">
<html xmlns="" >
    <title>Maintenance Mode - Outage Message</title>
    <h1>Maintenance Mode</h1>

    <p>We're currently undergoing scheduled maintenance. We will come back very shortly.</p>
    <p>Sorry for the inconvenience!</p>

    Adding additional hidden content so that IE Friendly Errors don't prevent
    this message from displaying (note: it will show a "friendly" 404
    error if the content isn't of a certain size).
    <h2>Site under maintenance...</h2> 
    <h2>Site under maintenance...</h2> 
    <h2>Site under maintenance...</h2> 
    <h2>Site under maintenance...</h2> 
    <h2>Site under maintenance...</h2> 
    <h2>Site under maintenance...</h2> 
    <h2>Site under maintenance...</h2> 
    <h2>Site under maintenance...</h2> 




Posted On Wednesday, March 28, 2012 4:25 PM | Comments (3)
Thursday, March 22, 2012
Show line breaks in asp:label inside gridview

To show line breaks in asp:label element or for that matter inside Gridview, do the following  in case of Mandatory/ Nullable fields.

<%# ((string)Eval("Details")).Replace("\n", "<br/>")
<%# FormatString(Eval("Details"))


In code behind, add the following FormatString function -


string FormatString(string strHelpMessage)


string rtnString = string.Empty;

if (!string.IsNullOrEmpty(strHelpMessage))

rtnString = strHelpMessage.Replace(

Environment.NewLine, "<br/>");

return rtnString;


Posted On Thursday, March 22, 2012 4:02 PM | Comments (2)
Page_BlockSubmit - reset it to False, if there is a scenario when page doesn't postback on validation error

Recently, I was facing a problem where if there was a validation error, and if I changed the state of checkbox it won't postback on first attempt. But when I uncheck and check again , it postbacks on second attempt...this is some quirky behaviour in .ASP.Net platform.

The solution was to reset Page_BlockSubmit flag to false and it works fine.

The following explanation is from


Submit button on the page is a member of vgMain, so automatically it will only run the validation on that group. A solution is needed that will run validation on multiple groups and block the postback if needed.


Include the following function on the page:

function DoValidation()
   //validate the primary group
   var validated = Page_ClientValidate('vgPrimary ');
   //if it is valid
   if (validated)
      //valid the main group
      validated = Page_ClientValidate('vgMain');
   //remove the flag to block the submit if it was raised
   Page_BlockSubmit = false;
   //return the results
   return validated;

Call the above function from the submit button’s OnClientClick event.

<asp:Button runat="server" ID="btnSubmit" CausesValidation="true" 
   ValidationGroup="vgMain" Text="Next" OnClick="btnSubmit_Click"
   OnClientClick="return DoValidation();"  />

What is Page_BlockSubmit

When the user clicks on a button causing a full post back, after running Page_ClientValidate ASP.NET runs another built in function ValidatorCommonOnSubmit. Within Page_ClientValidate, Page_BlockSubmit is set based on the validation. The postback is then blocked in ValidatorCommonOnSubmit if Page_BlockSubmit is true. No matter what, at the end of the function Page_BlockSubmit is always reset back to false.

If a page does a partial postback without running any validation and Page_BlockSubmit has not been reset to false, the partial postback will be blocked. In essence the above function, RunValidation, acts similar to ValidatorCommonOnSubmit. It runs the validation and then returns false to block the postback if needed. Since the built in postback is never run, we need to reset Page_BlockSubmit manually before returning the validation result.

Posted On Thursday, March 22, 2012 3:59 PM | Comments (2)
Tuesday, March 13, 2012
Update Query in SQL with SELECT statement
Table.col1 = other_table.col1, 
Table.col2 = other_table.col2 
ON = 
Posted On Tuesday, March 13, 2012 4:16 PM | Comments (3)
Monday, March 5, 2012
ThrowIfMaxHttpCollectionKeysExceeded error message when doing postback

Microsoft recently (12-29-2011) released an update to address several serious security vulnerabilities in the .NET Framework. One of the fixes introduced by MS11-100 temporarily mitigates a potential DoS attack involving hash table collisions. It appears this fix breaks pages that contain a lot of POST data. In our case, on pages that have very large checkbox lists. Why would this be the case?

There are some information on this limit to be at if your webpage was working fine earlier and it all of a sudden started crashing with the following message -

"Operation is not valid due to the current state of the object." whenever a post back is done.

the stack trace is

at System.Web.HttpValueCollection.ThrowIfMaxHttpCollectionKeysExceeded() at System.Web.HttpValueCollection.FillFromEncodedBytes(Byte[] bytes, Encoding encoding) at System.Web.HttpRequest.FillInFormCollection()

Then  probably you have crossed the default limit for the postdata collection key.

To overcome this limit, add the following line of code in you <appsetting> section of web.config

<add key="aspnet:MaxHttpCollectionKeys" value="2001" />

Hope this helps someone..           

Posted On Monday, March 5, 2012 4:05 PM | Comments (15)
Thursday, June 9, 2011
Date/Time formats in SQL Server

In SQL server a column with datatype of DateTime stores the time to the millisecond, but many times you just like to show the date and time only upto the second.

Although there are many ways to achieve this, I'm just going to show the couple of possible ways -



This would yield the current date time in YYYY-MM-DD HH:MM:SS (ofcourse depends on your regional setting)

2.) You can get the millisecond part from the getdate() function and subtract it with the getdate() to leave you with time only upto second.

SELECT DATEADD(ms, -DATEPART(ms,GetDate()),GetDate())

As I mentioned earlier, these are just some of the possible ways to acheive the desired result.

The post has some very good collection to acheive various formats in date/time in SQL.

Hope this helps!


Posted On Thursday, June 9, 2011 12:21 PM | Comments (0)
Wednesday, June 8, 2011
Detecting browser 'Refresh' from Code behind in C#

Browser 'Refresh' is always a cause of concern for the developers. It becomes even more worse when the page interacts with the database. As each refresh, if not handled, would lead to the database action repeated.

This could lead to inconsistency in database or even break the application.

One way could be to detect 'refresh' using javascript and disable F5 or right click..but there are so many other ways end-user can initiate 'Refresh' action for e.g. by using CTRL+R on the keyboard...

The best way to stop 'Refresh' calling your program again is to detect it at the server side and handle it..

The following code snippet detects 'Refresh' in page_load function

bool IsPageRefresh = false;

//this section of code checks if the page postback is due to genuine submit by user or by pressing "refresh"

if (!IsPostBack)


ViewState["ViewStateId"] = System.Guid.NewGuid().ToString();

Session["SessionId"] = ViewState["ViewStateId"].ToString();



if (ViewState["ViewStateId"].ToString() != Session["SessionId"].ToString())


IsPageRefresh = true;


Session["SessionId"] = System.Guid.NewGuid().ToString();

ViewState["ViewStateId"] = Session["SessionId"].ToString();


You can then use the 'IsPageRefresh' boolean flag in the code-behind to determine if it's a postback due to genuine User submit action or by browser 'Refresh'.

Hope this helps!

Posted On Wednesday, June 8, 2011 4:34 PM | Comments (9)
Catching Schema Errors in TRY..CATCH block in SQL Server

Recently I was writing a stored procedure in SQL Server 2005. The stored procedure was pretty straight forward with some couple of SQL DML statements inside TRY block and exception being handled and raised in CATCH block.

However, while testing the exception handling of the stored procedure, it didn't seem to go to CATCH block.

All I was trying was - Insert some data into some 'non-existent' table and catch the error in the CATCH block. I was expecting stored procedure to report 'Invalid object name' but it didn't...


SELECT * FROM NonExistentTable






Little bit of digging led me to the folowing link

It is a very good article on Try..Catch statement, according to this the Try/Catch block will not be able to detect any COMPILE errors which includes schema errors.

To be able to CATCH such errors, we should wrap the statements in dynamic SQL in TRY block .. this solved my problem..


DECLARE @sql nvarchar(100)

SET @sql = 'SELECT * FROM NonExistentTable'

EXEC sp_executesql @sql






Hope this helps someone too!


Posted On Wednesday, June 8, 2011 3:15 PM | Comments (1)
Tuesday, June 7, 2011
Dynamic table creation using SELECT INTO Clause in SQL

While working with SQL, you come across a scenario when you need to dump the resultset of a SELECT statement into either a temporary table or a placeholder table. I can imagine how tiresome it is to create a schema for the table beforehand and keep that updated with any additions/deletions to the SELECT statement.

To cut the long story short, SQL makes ones life easy with the help of a syntax "SELECT... INTO..."


SELECT column1, column2,  column3

INTO NewDynamicTable

FROM TableName

This would create a new table 'NewDynamicTable'  with 3 coumns and populate it with the resultset of the SELECT statement.

Tip - If you want to create just schema for the table and does not want to populate it with the results of the select statement - use a where clause in the SELECT statement which won't yield any results.

SELECT column1, column2

INTO NewTable

FROM TableName


Hope this helps!

Posted On Tuesday, June 7, 2011 12:07 PM | Comments (3)
Friday, June 3, 2011
Unsign the assembly for Warning VSP2013

Recently while performing a .Net unit testing on some .Net dll, I kept getting the below error -

'XXX is a strongly named assembly.  It will need to be re-signed before it can be executed.

Warning VSP2013 : Instrumenting this image requires it to run as a 32-bit process"......

A little of  google revealed that since its a strongly named assembly and while performing unit testing it needed to be re-signed or I need to remove the 'strong name' all together...

As I didn't had the orginal private key file with which the assembly was initially signed, I choose the latter option.

To de-sign the assembly -

Go to visual studio command prompt  (Start -> Progrmas -> Microsoft Visual Studio 2005/2008 -> Visual Studio tools -> Command prompt)

Type the following  -

SN -Vr Path_Of_Assembly

Make sure to sign the assembly again at the end of the task otherwise it may lead to some security issues.

SN -Vu Path_Of_Assembly

 Hope this helps!!


Posted On Friday, June 3, 2011 2:30 PM | Comments (0)
Friday, October 1, 2010
XML Serialization - example

Serialization and De-serialization is very important concept in .Net world. I won't go in-depth of what each of them means.

In short, Serialization means converting your .Net object into some preservable format like xml string.

De-serialization is the exact opposite of this and it means converting your string into C# object.

In this article, I will show you a very simple working example of Serialization.

Suppose you want to Serializa an object obj of type obj_Type

Step 1:  Include System.Xml.Serialization namespace in your code

Step 2: Put the following lines of code

XmlSerializer xml = new XmlSerializer(typeof(obj_Type));

System.IO.TextWriter txt = new System.IO.StreamWriter(@"C:\test.xml");

xml.Serialize(txt, obj);


Following execution of this code, your object would be serialized into an xml file.

Hope this helps !!

Posted On Friday, October 1, 2010 10:19 AM | Comments (1)
Tuesday, September 28, 2010
Having multiple tags in ASP.Net web page

Often we want to have more than one form in the web page, just like in HTML or in classic ASP - except ASP.Net throws an error saying 'Only one <Form> tag can exists in Body'.

The error message thrown by ASP.Net runtime compiler is not comprehensive, it should say that there can be only one <Form> tag with runat=server attribute.

This means we can have more than one form tags in our ASP.Net HTML but only one of them can be server side. Rest of the forms have to be client side HTML forms.

Hope this helps!

Posted On Tuesday, September 28, 2010 3:34 PM | Comments (2)
Friday, March 26, 2010
Various string manipulation functions in SQL Server 2000 / 2005

SQL Server provides a range of string manipulation functions. I was aware of most of those in back of the mind, but when I needed to use one, I had to dig it out either from SQL server help file or from google. So, I thought I will list some of the functions which performs some common operations in SQL server. Hope it will be helpful to you all.

Len (' String_Expression' ) - returns the length of input String_Expression.

Example - Select Len('Vipin')

Output - 5

Left ( 'String_Expression', int_characters )returns int_characters characters from the left of the String_Expression.  

Right ( 'String_Expression', int_characters ) - returns int_characters characters from the right of the String_Expression.  

Example - Select Left('Vipin',3), Right('Vipin',3)

Output -  Vip,  Pin

LTrim ( 'String_Expression' )removes spaces from left of the input 'String_Expression'

RTrim ( 'String_Expression' )removes spaces from right of the input 'String_Expression'

Note - To removes spaces from both ends of the string_expression use Ltrim and RTrim in conjunction

Example - Select LTrim(' Vipin '), RTrim(' Vipin ') , LTrim ( RTrim(' Vipin '))

Output - 'Vipin ' , ' Vipin' , 'Vipin'

(Single quote marks ' ' are not part of the SQL output, it's just been included to demonstrate the presence of space at the end of string.)

Substring ( 'String_Expression' , int_start , int_length )this function returns the part of string_expression. The expression returned is int_length long starting from int_start position. 

Example - Select Substring ( 'abcdef' , 2 , 3)

Output -  bcd

  1. if  'String_Expression'  is text - value returned is varchar
  2. if  'String_Expression'  is image - value returned is varbinary
  3. if  'String_Expression'  is ntext - value returned is nvarchar

Lower ( 'String_Expression' )  - converts string_expression into lower case.

Example - Select LOWER ( 'Vipin')  

Output - vipin

Upper ( 'String_Expression' )  - converts string_expression into upper case.

Example - Select UPPER ( 'Vipin')  

Output - VIPIN

Reverse ( 'String_Expression' )  - Reverses the string expression from right to left

Example - Select REVERSE ( 'Vipin')  

Output - nipiV

Replace ( 'String_Expression', 'FIND_String', 'REPLACE_String' )  - This function is pretty much similar to 'Find-Replace' dialog box found in editors. This would replace all occurrences of 'FIND_String' with 'REPLACE_String' in 'String_Expression'

Example - Select REPLACE ('Vipin', 'i', 'X')

Output - VXpXn

Also note, string to find is Case-Insensitive.

REPLICATE ( 'String_Expression' , 'Integer_Expression' )  - This function is used to repeat 'String_Expression' for a specified 'Integer_Expression'.

Example - Select REPLICATE ( 'Vipin',2)  

Output - VipinVipin

These were just some of the many string manipulation functions available in SQL Server 2000/2005. More comprehensive details are available on comprehensive SQL server help file. However, you can also refer to examples of the functions given here.

Posted On Friday, March 26, 2010 3:07 PM | Comments (2)