Parsing the comma separated values into a temporary table

-
(Works in both SQL Server 7.0 and 2000) 

Declare @OrderList varchar(500)
set @OrderList = 'Order1,Order2,,Order3,,,Order4,Order5,';

drop TABLE #TempList

CREATE TABLE #TempList
(
   OrderID varchar(20)
)

DECLARE @OrderID varchar(10), @Pos int
SET @OrderList = LTRIM(RTRIM(@OrderList))+ ','
SET @Pos = CHARINDEX(',', @OrderList, 1)

IF REPLACE(@OrderList, ',', '') <> ''
BEGIN
   WHILE @Pos > 0
   BEGIN
       SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1)))
       IF @OrderID <> ''
       BEGIN
           INSERT INTO #TempList (OrderID) VALUES (@OrderID)
       END
       SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)
       SET @Pos = CHARINDEX(',', @OrderList, 1)
   END
END

SELECT * FROM #TempList t

+-+-+-+-+-+
OUTPUT
+-+-+-+-+-+

OrderID
----------
Order1
Order2
Order3
Order4
Order5

Comments

Post a Comment

Popular posts from this blog

Managed Code and UnManaged Code

-
Managed Code is produced by (vb.net,c#,j#) .net framework language, which is under the control of CLR (Common Language Runtime).Garbage collector run automatically in managed code. Unmanaged Code is produced by third party language such as C++ can be used to write such applications, which does not run under the control of CLR.Garbage collector will not run in case of unmanaged code. Examples of Unmanaged code are Access low-level functions of the operating system. Background compatibility with code of VB, ASP and COM.
Read more

Select nth Row from Table in SQL Server

-
DECLARE @RowNumber INT = 9 WITH TableWithRow AS (       SELECT        ( ROW_NUMBER () OVER ( ORDER BY employees . EmployeeID )) as ROW ,        FirstName , LastName                 FROM employees ) SELECT * FROM TableWithRow WHERE ROW = @RowNumber Output: ROW                  FirstName  LastName -------------------- ---------- -------------------- 9                    Ashvin     Padhiyar (1 row(s) affected)  Note: ROW_NUMBER () is an analytic function. It assigns a unique number to each row to which it is applied, in the ordered sequence of rows specified in the order_by_clause , beginning with 1.  
Read more

Page Life Cycle in ASP.NET

-
Image
Life cycle when the page processed during a postback The events associated with the relevant page cycle phases are: Page Initialization: Page_Init View State Loading:LoadViewState Postback data processing: LoadPostData Page Loading: Page_Load PostBack Change Notification: RaisePostDataChangedEvent PostBack Event Handling: RaisePostBackEvent Page Pre Rendering Phase: Page_PreRender View State Saving: SaveViewState Page Rendering: Page_Render Page Unloading: Page_UnLoad The processing sequence in which a page is processed during a postback event is: Initializing: During this phase, the server creates an instance of the server control Loading view state: During this phase, the view state of the control posted by the client is reloaded into the new instance of the control. Loading : During this phase, the instance of the control is loaded onto the page object in which it is defined. Loading the postback data : During this phase, the server s
Read more

Abstract Class and An Interface

-
Abstract Class : The class which cannot be instantiated is known as an Abstract Class . So an object of it cannot be created. Hence it must be inherited. Can't be instantiated. Must be inherited. It may have Concrete Methods & Abstract Methods. An Abstract Class can have only Abstract Method. An Abstract Method must be overridden.     public abstract class AbstractA     {         public abstract string AbstractProperty { get ; set ; }         public abstract void AbstractFunction1();         public virtual void AbstractFunction2()         {             Console .WriteLine( "AbstractA : AbstractFunction2()" );         }         public void AbstractFunction3()         {             Console .WriteLine( "AbstractA : AbstractFunction3()" );         }         public void AbstractFunction4()         {             Console .WriteLine( "AbstractA : AbstractFunction4()" );         }     }     pu
Read more

SQL Server 2005 with XML Parameters

-
We frequently have requirement like Passing IDs in comma saperated value to the sql server for further processing. Generally what we do is just create loop and get the value from it and ... But SQL 2005 comes up with XQuery. Using this you can easly pass such type of data also some complex thing too. For example... DECLARE @productIds xml SET @productIds ='<Products><id>9</id><id>18</id><id>27</id></Products>' SELECT ParamValues.ID.value ('.','VARCHAR(20)')FROM @productIds.nodes('/Products/id')as ParamValues(ID) Which gives us the following three rows: 9 18 27
Read more

Print Function By JavaScript

-
function PrintContent( Obj) { PrintWin=window.open('','PrintWin','directories=no, location=no, menubar=no, resizable=no, status=no, toolbar=no, scrollbars=no, width=10, height=10, left=1050, top=800'); PrintWin.document.write(" "); PrintWin.document.write(document.getElementById(Obj.id).outerHTML); PrintWin.location.reload(); PrintWin.print(); PrintWin.close(); } /************* Function call ***************/ btnPrint.Attributes.Add( "OnClick", "PrintContent(" + TblPrint.ClientID + ");");
Read more

Auto Refresh .aspx page - ASP.NET

-
Method 1: Response.AddHeader To Refresh Web page After every 5 Seconds You can add following code, Response.AddHeader("Refresh", "5"); Cricket Sites, Stock Exchange sites use similar logic :) Method 2: In body Tag, window.setTimeout The 1000 = 1 Second... < body onload ="window.setTimeout('window.location.reload()',1000);" > Method 3: In Meta Tag Theres also a meta tag that you can define on the head, but not sure wheter it refreshes even if the content has not finished loading or if it starts counting when the head section loaded. Code would be something like that: < meta content ="600" http-equiv ="refresh" > Method 4: Timer Control : Microsoft ASP.NET 2.0 AJAX Extensions server control In following example, the timer interval is set to 10 seconds <%@ Page Language="C#" AutoEventWireup="true" %> <! DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN&q
Read more

Overview of MVC

-
Overview of the ASP.NET MVC - Introduction to ASP.NET MVC -  ASP.NET MVC Version History -  Life Cycle of MVC request -  Role of Model, View, and Controller -  Works Behaviour of ASP.NET MVC -  Benefits MVC Architecture - Razor View Engine - Essential Language Features & Helpers - Basic Helpers - Strongly-Typed Helpers - Creating Custom Helpers - Declarative Helpers The MVC Pattern - The MVC Design Pattern - Working with Controllers - Model Templates - MVC State Management - Working with URLs and Routing (URL Routing) - Bundling and Minification Working with Controllers - Controller Actions - Controller and ActionResult Types - Passing Data from View to Controller - Action Filters & Custom Action Filter - Action Selectors HttpGet and HttpPost Razor Views - Razor View Engines and Syntax - Working with Layout Pages - Views & Partial Views - ViewData and ViewBag - Strongly-Typed Views - Working with Areas Model Binders - Behaviour
Read more