Count Duplication

create table books
(
 id int identity(1,1),
 custname varchar(25),
 bookname varchar(25)
)

INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( 'naimish','five point someone')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( 'ravi', 'five point someone')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( 'jugal','five point someone')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( 'jugal','3 mistakes of my life ')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( 'naimish','3 mistakes of my life ')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( 'nikhi', 'satya na prayogo')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( 'ram', 'ravan')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( 'misi','evil')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( 'michel','evil')

Ans :-


SELECT bookname,COUNT(*) NoOfbooks
FROM books
GROUP BY bookname
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC

Split Function

create FUNCTION Naimish_split
( @string varchar(max),  @Delimeter varchar(2))
RETURNS @temp TABLE
(
id int identity(1,1),
name varchar(25)
)
AS
begin
set @string = replace(@string,' ','')
declare @count int , @countDest int,@no int
select @count = 0,@countDest = LEN(@String) - LEN(REPLACE(@String, @Delimeter, ''))
while @count < @countDest
begin
set @no =  charindex(@Delimeter,@string,1)-1
insert into @temp (name) values(left(@string,@no))
set @string = right(@string,len(@string)- (@no+1))
set @count = @count + 1
end
insert into @temp(name) values(@string)
return
end

--select * from Naimish_split('Prashant,naimish,ravi,vimal,jugal,Bhadresh,mohit' , ',')

O/P: