Split Function Example in SQL Server

Split Function Example in SQL Server to Split Comma Separated String into Table 

Here I will happy to explain simple split function in SQL Server to split comma
separated string into table values in SQL Server database or How to split comma separated string with custom split() function in SQL Server. 


To split comma separated string[abc,xyz,pqr] in SQL Server we need to write custom method for that we need to create one function like as shown below

CREATE FUNCTION dbo.Split(@String nvarchar(2000), @Delimiter char(1))
RETURNS @Results TABLE (value nvarchar(2000))
AS
BEGIN
DECLARE @INDEX INT
DECLARE @SLICE nvarchar(2000)
--ENTER FIRST TIME IN LOOP
SELECT @INDEX = 1
WHILE @INDEX !=0
BEGIN
-- GET THE INDEX OF THE FIRST OCCURENCE OF THE CHARACTER TO SPLIT STRING
SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
-- NOW SET EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
IF @INDEX !=0
SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
ELSE
SELECT @SLICE = @STRING
-- PUT THE VALUE INTO THE RESULTS SET
INSERT INTO @Results(value) VALUES(@SLICE)
-- CHOP THE ITEM REMOVED OFF THE MAIN STRING
SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)
-- BREAK OUT IF WE GOT FINAL RESULT
IF LEN(@STRING) = 0 BREAK
END
RETURN
END
 

  Once we create custom function Split() for comma separated string in SQL Server than  run sample query like as shown below 

SELECT items FROM [dbo].[Split] ('ABC,XYZ,PQR', ',')

 Once we run query we will get output like as shown below 

 OUTPUT: 

  

SQL - Reset Identity Column Value to Start With 1 in SQL Database

 SQL - Reset Identity Column Value to Start With 1 in SQL Database 


Here I will illustrate how to reset  identity column value in SQL server or change or rest identity column value to start with 1 in SQL server. Then again reseed identity column value in SQL server.

After setting identity property on particular column I inserted few records in Sql Database table and
that value automatically increase whenever I inserted data that would be like this 

 

 

 Ex: Above table contains total 6 records after delete all these records if I insert new record CountryID value

 will start from 7.

For reset identity column value and start value from “1” during insert new records we need to
write query for reset identity column value. Check below Query

DBCC CHECKIDENT (Table_Name, RESEED, New_Reseed_Value)

Table_Name is name of your identity column table like tbl_Country

RESEED specifies that the current identity value should be changed.

New_Reseed_Value is the new value to use as the current value of the identity column like CountryID. 

 EX: DBCC CHECKIDENT ('tbl_Country', RESEED, 0) 

Once we run the above query it will reset the identity column(CountryID) value in tbl_country
table and starts identity column value from “1”

how to create n-tier architecture in asp.net 4.0

 

 What is N-tier Architecture? 

In software engineering, n-tier architecture is a client–server architecture in which presentation, application processing, and data management functions are logically separated. For example, an application that uses middleware to service data requests between a user and a database employs multi-tier architecture. The most widespread use of multi-tier architecture is the three-tier architecture.

 following are steps to create n-tire architecture in asp.net:-

1.  Select Project type of Blank Solution

  * Open visual studio 2010

  * Select following path

  * file -> new -> project

  * Select other project type and select visual studio solution , in the right panel select blank solution 

  

* Provide name to solution

* Select location

Now we have to create data layer(class library) to improve the modularity.

2.  Create/Add Layers in Solution

 2.1 add data layer to solution 

* Open solution explorer and right click on solution.

* Select Add -> New Project

* Select visual c# -> windows  

* In right panel select class library

* Provide name to this layer  

 

 2.2 add web site to solution

* Open solution explorer and right click on solution.

* Select Add -> New Web Site

* Select ASP.NET Empty Web Site 

* Browse to your application location 

* Provide name to this layer

2.3 Add first page in your web site

* Right click on Web site in solution Explorer

* Select Add New Item

* Select Web Form (.aspx)

* provide name to this first page like index.aspx / default name is Default.aspx

 

 

 -Thank you