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

SET NOCOUNT

Stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set.

Syntax: SET NOCOUNT { ON | OFF } 

Remarks

When SET NOCOUNT is ON, the count is not returned. When SET NOCOUNT is OFF, the count is returned.

The @@ROWCOUNT function is updated even when SET NOCOUNT is ON.

Examples

The following example prevents the message about the number of rows affected from being displayed.

USE AdventureWorks2012;

GO

SET NOCOUNT OFF;

GO

-- Display the count message.

SELECT TOP(5)LastName

FROM Person.Person

WHERE LastName LIKE 'A%';

GO

-- SET NOCOUNT to ON to no longer display the count message.

SET NOCOUNT ON;

GO

SELECT TOP(5) LastName

FROM Person.Person

WHERE LastName LIKE 'A%';

GO

-- Reset SET NOCOUNT to OFF

SET NOCOUNT OFF;

GO

Read More

Built-in Functions (Transact-SQL)

CHARINDEX - Searches an expression for another expression and returns its starting position if found.

DECLARE @FullName VARCHAR(100)

SET @FullName = 'Clark Kent'

select CHARINDEX(' ', @FullName)
Output – 6

STUFF - The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.
--Syntax STUFF(character_expression , start , length , replaceWith_expression)

DECLARE @FullName VARCHAR(100)

DECLARE @Alias VARCHAR(20)

SET @FullName = 'Clark Kent'

SET @Alias = ' "Superman" '

SELECT STUFF(@FullName, CHARINDEX(' ', @FullName), 1, @Alias) AS [FullName]

Output - Clark "Superman" Kent

SELECT STUFF('abcdef', 2, 3, 'ijklmn');

Output - aijklmnef 

Replace -  Replaces all occurrences of a specified string value with another string value.

Syntax - REPLACE (string_expression , string_pattern , string_replacement)

SELECT REPLACE('ABCDEFGH','AB','K')

Output – KCDEFGH

REPLICATE - Repeats a string value a specified number of times.

Syntax- REPLICATE ( string_expression ,integer_expression ) 

SELECT 'ID'+ REPLICATE('0', 4) ;-- output:ID0000

Reverse - Returns the reverse order of a string value.

Syntax - REVERSE ( string_expression )

SELECT REVERSE('ABCD') 

Output – DCBA

ASCII – returns ASCII value of a specified string

SELECT ASCII('A')  

Output – 65

Concat – Returns a string that is the result of concatenating two or more string values.

select CONCAT('AB','C'),CONCAT ( 'Happy ', 'Birthday ', 11, '/', '25' )

Output- ABC   Happy Birthday 11/25

LEFT - Returns the left part of a character string with the specified number of characters.

SELECT LEFT('abcdefg',2)      Output - ab

SELECT RIGHT('abcdefg',2)      Output - fg

SELECT LEN('abcdefg')      Output – 7 

SELECT LOWER('ABCDEFG') AS Lower      Output - abcdefg

SELECT UPPER('abcdefg') as uppar;      Output – ABCDEFG

LTRIM - Returns a character expression after it removes leading blanks.

select LTRIM(' abcdefg dfj')     Output – abcdefg dfj

select RTRIM('abcdefg dfj ')     Output – abcdefg dfj

STR - Returns character data converted from numeric data.

Syntax - STR ( float_expression [ , length [ , decimal ] ] )

SELECT STR(123.45, 6, 1);

GO

Output - 123.5

SUBSTRING - Returns part of a character, binary, text, or image expression in SQL Server.

Syntax- SUBSTRING ( expression ,start , length )

SELECT x = SUBSTRING('abcdef', 2, 3);

Output -  bcd

CHAR – Converts an int ASCII code to a character.

SELECT char(65)

Output – A

SPACE – Returns a string of repeated spaces.

Syntax- SPACE ( integer_expression )

SELECT  SPACE(20)+'abcdefg'+ ','

Output -                      abcdefg,

Read More

Different Types of SQL Joins

S

ql joins are used to fetch/retrieve data from two or more data tables, based on a join condition. A join condition is a relationship among some columns in the data tables that take part in Sql join. Basically data tables are related to each other with keys.

I have 2 Tables  in sql server,

Table 1 with Primary Key

Table2 with Foreign Key

Types of Joins

In Sql Server we have only three types of joins. Using these joins we fetch the data from multiple tables based on condition.

Inner Join

Inner join returns only those records/rows that match/exists in both the tables. Syntax for Inner Join is as

Outer Join

  

    refer the  following url : Click here

Cross Join

Cross join is a cartesian join means cartesian product of both the tables. This join does not need any condition to join two tables. This join returns records/rows that are multiplication of record number from both the tables means each row on left table will related to each row of right table. Syntax for right outer Join is as :

Self Join 

Self join is used to join a database table to itself, particularly when the table has a Foreign key that references its own Primary Key. Basically we have only three types of joins : Inner join, Outer join and Cross join. We use any of these three JOINS to join a table to itself. Hence Self join is not a type of Sql join.

To understand Self Join, suppose we following two tables and data in these two tables is shown in figure.

CREATE TABLE emp

(

id int NOT NULL primary key,

name varchar(100) NULL,

designation varchar(50) NULL,

supid int foreign key references emp(id) ) -- In this table we have a Foreign key supid that references its own Primary Key id. We use it for Self Join

INSERT INTO emp(id,name,designation) VALUES(1,'mohan','Manger')

INSERT INTO emp(id,name,designation,supid) VALUES(2,'raj kumar','SE',1)

 INSERT INTO emp(id,name,designation) VALUES(3,'bipul kumar','Manager')

 INSERT INTO emp(id,name,designation,supid) VALUES(4,'mrinal kumar','SE',2)

 INSERT INTO emp(id,name,designation,supid) VALUES(5,'jitendra kumar','SE',2)

 select * from emp

Code for SelfJoin is : 

Regards: Suraj K Mad.

Read More

Basics of SQL Commands

SQL commands are a set of instructions that are used to interact with the database like Sql Server, MySql, Oracle etc. SQL commands are responsible to create and to do all the manipulation on the database. These are also responsible to give/take out access rights on a particular database

Sql Commands Category

We have different sql commands for different-different purpose. We can grouped Sql Commands into five major categories depending on their functionality.

01.       Data Definition Language (DDL)

These SQL commands are used to create, modify, and drop the structure of database objects like table, view, procedure, indexes etc. In this category we have CREATE, ALTER, DROP and TRUNCATE commands.

Note:-

1.            Only with DDL commands we need to write keyword (like table, procedure, view, index, function) with the syntax of command.

2.            These commands are used to create/modify the structure of the database object.

Example:

CREATE TABLE TABLE_NAME

(

COL1 VARCHAR(10),

COL2 VARCHAR(20),

);

--Here "TABLE" is a keyword that is used to create table "TABLE_NAME"

CREATE VIEW VIEW_NAME

AS

BEGIN

 SELECT * FROM EMP_TABLE

END

--Here "VIEW" is a keyword that is used to create VIEW "VIEW_NAME"

02.      Data Manipulation Language (DML)

These SQL commands are used to store, modify, and delete data from database tables. In this category we have INSERT, UPDATE, and DELETE commands.

03.     Data Query Language (DQL)

These SQL commands are used to fetch/retrieve data from database tables. In this category we have only SEELCT command.

04.     Transaction Control Language (TCL)

These SQL commands are used to handle changes which affect the data in database. Basically we use these commands with in the transaction or to make a stable point during changes in database at which we can rollback the database state if required. In this category we have SAVEPOINT, ROLLBACK and COMMIT commands.

05.      Data Control Language (DCL)

These SQL commands are used to implement security on database objects like table,view,stored procedure etc. In this category we have GRANT and REVOKE commands.

Note:-

Grant Command : This command is used to give permission to specific users on specific database objects like table, view etc.

Revoke Command : This command is used to take out permission from specific users on specific database objects like table, view etc.

Read More