UDF For Splitting Comma Seperated Values

CREATE FUNCTION [dbo].[fnSplit](   

    @sInputList VARCHAR(8000) -- List of delimited items   

  , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items   

) RETURNS @List TABLE (item VARCHAR(8000))   

   

BEGIN   

DECLARE @sItem VARCHAR(8000)   

WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0   

 BEGIN   

 SELECT   

  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),   

  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))   

    

 IF LEN(@sItem) > 0   

  INSERT INTO @List SELECT @sItem   

 END   

   

IF LEN(@sInputList) > 0   

 INSERT INTO @List SELECT @sInputList -- Put the last item in   

RETURN   

END 

 Now Execute the Function

 SELECT * FROM dbo.[fnSplit]('1,2,3',',')

For Output see the Screenshot

 Another Function for the Same Is:

CREATE FUNCTION [dbo].[Split](@String nvarchar(4000), @Delimiter char(1)) 

RETURNS @Results TABLE (Items nvarchar(4000)) 

AS 

BEGIN 

DECLARE @INDEX INT 

DECLARE @SLICE nvarchar(4000) 

-- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z 

--     ERO FIRST TIME IN LOOP 

SELECT @INDEX = 1 

WHILE @INDEX !=0 

BEGIN 

-- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER 

SELECT @INDEX = CHARINDEX(@Delimiter,@STRING) 

-- NOW PUSH 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 ITEM INTO THE RESULTS SET 

INSERT INTO @Results(Items) VALUES(@SLICE) 

-- CHOP THE ITEM REMOVED OFF THE MAIN STRING 

SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX) 

-- BREAK OUT IF WE ARE DONE 

IF LEN(@STRING) = 0 BREAK 

END 

RETURN 

END 

 Now Execute the Function

  

SELECT * FROM dbo.[Split]('1-2-3','-')

Read More

Schema in SQL Server 2012

Introduction

In this article I describe schemas in SQL Server 2012. A schema is like a container that contains database objects like tables, views etc. So let us learn about schemas, creation of schemas, how to alter schemas and how to drop schemas.

Schema:

A schema is like a namespace in C# that contains database objects. All objects within a schema must have a unique name. And the name of the schema should also be unique in the database. When we create a table, view or other object then by default it goes in the dbo schema.

Syntax:

create schema schema_name 
go
<schema element>
{table defenation, view defenation etc}

Example:

create schema ss1
go create table ss1.emp(empId int,empname varchar(15))
go 
insert into ss1.emp values(1,'d')
go 
select * from ss1.emp
Output:

Creation of table in schema:

create table ss1.emp2(empAdd varchar(15))
Output:

 Creation of view in schema:

create view ss1.v
as select * from ss1.emp
Output:

select * from ss1.v

Alter schema:

First of all we are creating a schema and than we alter that schema:

create schema ss2go
alter schema ss2 transfer ss1.emp 
go
select * from ss2.emp
Output:

Dropping a Schema:

drop table ss2.emp
go
drop schema ss2
Output:

Summary:In this article I described schemas in SQL Server. I hope this article has helped you to understand this topic. Please share if you know more about this. Your feedback and constructive contributions are welcome.

Read More

Delete duplicate rows in SQL Server

Introduction

Some time some duplicate data may inserted in the SQL table. But its harmful for you and your application too. So how to remove the duplicate rows in your table.

Description

Take a look in the following table content. Its having duplicate rows.

Row 1,2,3 and 4 are duplicate. So we have to remove one row and keep one in the table.

Now question is how to do?

To solve this problem take a look at the following code. This will solve this problem.

  WITH tblTemp as

(

SELECT ROW_NUMBER() Over(PARTITION BY Name,Department ORDER BY Name)

   As RowNumber,* FROM emp

)

DELETE FROM tblTemp where RowNumber >1

After run these code show the table data, it will all unique rows present.

Read More