August 12, 2008 at 10:18 am
is there any command which can count how many number of times does the word repeat such as
for eg ( abc,hq,abc,hq) i have this string so i need to write a function in such a way that when i just pass the unique id i shall get this string then under this when i pass abc i need to get as 2 and hq as 2 and def as o can u plz let me know its urgent ....
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
August 12, 2008 at 10:37 am
like this , use count and group by your field.
create table #a(text varchar(20))
insert #a values('abc')
insert #a values('abc1')
insert #a values('abc')
insert #a values('abc1')
insert #a values('abc')
select count([text]) , [text] from #a group by [text]
kgunnarsson
Mcitp Database Developer.
August 12, 2008 at 11:54 am
You need to break the comma separated string to a temp table and then you need to group by to take the count.
You can use following function for the same:
/****** Object: UserDefinedFunction [dbo].[fn_String_Split_And_Count] Script Date: 08/12/2008 10:47:56 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_String_Split_And_Count]
(@i_StringToSplit varchar(4000),
@i_Seperator char(1) )
RETURNS @lp_Result TABLE
(value varchar(32))
AS
BEGIN
/*------------SPLIT FUNCTIONALITY FOR THE PASSED STRING----------------------------------*/
DECLARE @lv_str as varchar(2000)
--DECLARE @i_Seperator as char(1)
DECLARE @start int
DECLARE @len int
DECLARE @pos int
DECLARE @val varchar(20)
SET @pos = 1
SET @i_Seperator = ','
SET @lv_str=@i_StringToSplit
SET @start = 1
SET @len = len(@lv_str)
--print ' Lenght '+cast( @len as varchar )
--WHILE @start <= @len
WHILE (@len >= 0)
BEGIN
SET @pos = charindex(@i_Seperator,@lv_str,1)
--print ' Position '+cast(@pos as varchar) +' Start:'+cast(@start as varchar)+
--' Len:'+cast(@len as varchar)
IF @pos = 0
BEGIN
SET @val = substring(@lv_str,1,@len)
IF LEN(@val) <> 0
INSERT INTO @lp_Result (value) values(@val)
--print ' value :'+@val+':'
break;
END
ELSE
BEGIN
SET @val = substring(@lv_str,1,@pos-1)
--print ' value :'+@val+':'
SET @len = @len - @pos
IF LEN(@val) <> 0
INSERT INTO @lp_Result (value)values(@val)
SET @lv_str= substring(@lv_str,@pos+1, @len )
--SET @start = @pos
--print @lv_str
END
END
RETURN
END
---------------------------------------------
EXAMPLES AND RESULTSETS:
SELECT * FROM [fn_String_Split_And_Count] ('abc,hq,abc,hq',',')
--VALUE
abc
hq
abc
hq
SELECT VALUE, COUNT(VALUE) AS COUNT FROM [fn_String_Split_And_Count] ('abc,hq,abc,hq',',')
GROUP BY VALUE
--VALUECOUNT
abc2
hq2
SELECT VALUE, COUNT(VALUE) AS COUNT FROM [fn_String_Split_And_Count] ('abc,hq,abc,hq',',')
WHERE VALUE = 'hq'
GROUP BY VALUE
--VALUECOUNT
hq2
---------------------------
Hope this helps 😉
August 12, 2008 at 12:10 pm
thankz guys....
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
August 12, 2008 at 12:20 pm
here's faster alternative built on the attached function fListToVarchars(). (it has embedded less than characters and never displays properly.) put it in your common/global db:
USE [global]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create function [dbo].[fCountOccurences]( @list varchar(max), @item varchar(255), @delim varchar(16) )
returns int
begin
declare @CT int
if ((','+@list+',') like ('%,'+@item+',%'))
select @CT = count(*) from dbo.fListToVarchars(@list,@delim) where item = @item
return isnull(@ct,0)
end
[font="Courier New"]select dbo.fCountOccurences('abc,hq,abc,hq,red','ab',','),
dbo.fCountOccurences('abc,hq,abc,hq,red','abc',','),
dbo.fCountOccurences('abc,hq,abc,hq,red','hq',','),
dbo.fCountOccurences('abc,hq,abc,hq,red','red',',')[/font]
results in 0, 2, 2, 1
August 12, 2008 at 12:39 pm
thanks sarang and collins i am done with that but now i have an other issue ....
i have got a string (32323,3232,32,3232) i have some lookup tables for this .....
i have two values which i willbe passing one is unique id from which i shall get the above string ...but what i need is i need to lookup those string with other value which i am passing ...i shall pass the other variable as bank so i need to lookup with table if any of these values comes under bank and i need to even include description column in it from that table so i need to get like
loan;finance---so it means that in the above string values one is from loan and the other from finance---
i need to write another function for these...
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
August 22, 2008 at 9:48 am
i have an issue here what if i have abc,hq,abchq as one type as i got
abc,hq ---when i wrote a function for this then i got as
abc -1
hq -1
abchq-1
how can i solve this issue plz urgent
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply