Site Sections
Posts
23
Comments
30
Trackbacks
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 * FROM
(
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 (0)
Tuesday, March 5, 2013
Reseed Identity column in SQL Server
Use the below SQL command to reset the seed on the identity column -

DBCC CHECKIDENT('TABLE_NAME', RESEED, 'RESEED_VALUE')

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 

DBCC INPUTBUFFER(SP_ID)

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.

DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = 61
SELECT TEXT
FROM sys.dm_exec_sql_text(@sqltext)
GO



Posted On Monday, March 4, 2013 3:32 PM | Comments (2)
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 (0)
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

SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, 
last_user_update, *

FROM sys.dm_db_index_usage_stats

WHERE database_id = DB_ID( 'DB_Name')
AND OBJECT_ID=OBJECT_ID('table_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 (2)
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.

SELECT  CONVERT(VARCHAR(30),so.name)    AS TABLE_NAME,
        COALESCE(Unused.IdxCount, 0)    AS IDXCOUNT,
        COALESCE(Unused.UnusedCount, 0) AS UNUSEDCOUNT,
        COALESCE(Missing.MICOUNT, 0)    AS MISSINGCOUNT,
        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
LEFT JOIN

    (   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
LEFT JOIN ( SELECT  d.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
            )Missing
ON      Missing.OBJECT_ID = so.OBJECT_ID
WHERE   so.type_desc = ‘USER_TABLE’
AND     (Missing.MICOUNT > 0
        OR Unused.UnusedCount > 0)
ORDER BY UnusedPct DESC 

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

This returns the following in sample database = 

TABLE_NAME IDXCOUNT UNUSEDCOUNT MISSINGCOUNT UnusedPct

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)
SET DATEFORMAT in SQL
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)

 

SET DATEFORMAT MDY

INSERT INTO #temp 

VALUES ('09/28/2007')

SET DATEFORMAT YDM

INSERT INTO #temp 

VALUES ('2007/28/09')

SET DATEFORMAT YMD

INSERT INTO #temp 

VALUES ('2007/08/28')

SET DATEFORMAT DMY

INSERT INTO #temp 

VALUES ('28/12/2006')


SELECT DateSample

FROM #temp 

DROP TABLE #temp 



This would return the following output -

DateSample

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.

Vipin

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" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head>
    <title>Maintenance Mode - Outage Message</title>
</head>
<body>
    <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> 
      
-->
</body>
</html>

 

 

 

Posted On Wednesday, March 28, 2012 4:25 PM | Comments (0)
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.

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

 

In code behind, add the following FormatString function -

protected

 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

http://lionsden.co.il/codeden/?p=137&cpage=1#comment-143

 

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.

Solution

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 (0)
Tuesday, March 13, 2012
Update Query in SQL with SELECT statement
UPDATE 
    Table 
SET 
    Table.col1 = other_table.col1, 
    Table.col2 = other_table.col2 
FROM 
    Table 
INNER JOIN 
    other_table 
ON 
    Table.id = other_table.id 
Posted On Tuesday, March 13, 2012 4:16 PM | Comments (2)
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 1000....so 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

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

Hope this helps someone..           

Posted On Monday, March 5, 2012 4:05 PM | Comments (4)
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 -

1.)

SELECT CONVERT(VARCHAR,GETDATE(),120)

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 www.sql-server-helper.com/tips/date-formats.aspx 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();

}

else
 
{

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 (3)
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...

BEGIN TRY

SELECT * FROM NonExistentTable

END TRY 

BEGIN CATCH

INSERT INTO EXCEPTION (Number, Message)

VALUES (ERROR_NUMBER(), ERROR_MESSAGE())

END CATCH

Little bit of digging led me to the folowing link

http://www.codeproject.com/KB/database/try_catch.aspx

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..

BEGIN TRY

DECLARE @sql nvarchar(100)

SET @sql = 'SELECT * FROM NonExistentTable'

EXEC sp_executesql @sql

END TRY

BEGIN CATCH

INSERT INTO EXCEPTION (Number, Message)

VALUES (ERROR_NUMBER(), ERROR_MESSAGE())

END CATCH

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..."

E.g.

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

WHERE 1=2

Hope this helps!

Posted On Tuesday, June 7, 2011 12:07 PM | Comments (1)
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);

txt.close();

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)
Thursday, March 25, 2010
Find the occurrence of word/character in SQL column with wildcard character - PATINDEX

CharIndex and PatIndex both can be used to determine the presence of character or string within sql column data. Both returns the starting position of the first occurrence of the character/word within expression.

However, one major difference between CharIndex and PatIndex is that later allows the use of wild card characters while searching for character or word within column data.

Also, Patindex is useful for searching within Text datatype.

Allowed wild card characters are % and _ .

" % "  - use it for any number of characters

" _ "  - use it for a single character.

Syntax

PATINDEX('%pattern%', string_expression)

Note - it's mandatory to include pattern within %% characters.

  • returns starting position of occurrence of pattern, if found.
  • returns 0, if not found
  • returns NULL , if either pattern or string_expression is null.

Example

  1. SELECT fldname FROM tblUsers WHERE PatIndex('%v_pin%', fldname) > 0

 

Posted On Thursday, March 25, 2010 5:35 PM | Comments (5)
Tuesday, March 23, 2010
How to find the occurrence of particular character in string - CHARINDEX

 

 

Many times while writing SQL, we need to find if particular character is present in the column data. SQL server possesses an in-built function to do this job -

Syntax 

CHARINDEX(character_to_search, string [, starting_position])

Returns the position of the first occurrence of the character in the string.

NOTE - index starts with 1. So, if character is at the starting position, this function would return 1.

Returns 0 if character is not found.

Returns 0 if 'string' is empty.

Returns NULL if string is NULL.

Example

SELECT CHARINDEX('a', fname) a_First_occurence,

CHARINDEX('a', fname, CHARINDEX('a', fname)) a_Second_occurrence

FROM Users

WHERE fname = 'aka unknown'

OUTPUT

a_First_occurence a_Second_occurrence
1 3

 

 

Posted On Tuesday, March 23, 2010 3:48 PM | Comments (0)
Archives
Post Categories