Count occurences of a value in a field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Count occurences of a value in a field

Author

Topic

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2009-10-28 : 10:59:18

Hi

Is there a simple SQL function to count the occurences of a value in a field?

i.e
Field1:
f;fd;fdie;eytue

Count of ";" = 3

Thanks!

dnf999

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2009-10-28 : 11:19:07

Ooh I've just found a function to do it :)

CREATE FUNCTION [dbo].[ufn_CountChar] ( @pInput VARCHAR(1000), @pSearchChar CHAR(1) )
RETURNS INT
BEGIN

DECLARE @vInputLength INT
DECLARE @vIndex INT
DECLARE @vCount INT

SET @vCount = 0
SET @vIndex = 1
SET @vInputLength = LEN(@pInput)

WHILE @vIndex <= @vInputLength
BEGIN
IF SUBSTRING(@pInput, @vIndex, 1) = @pSearchChar
SET @vCount = @vCount + 1

SET @vIndex = @vIndex + 1
END

RETURN @vCount

END

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-10-28 : 11:19:25

declare @field varchar(20)

set @field = ';fd;fdie;eytue'

SELECT [count] = len(@field) - len(REPLACE(@field,';',''))

Jim

Everyday I learn something that somebody else already knew

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2009-10-29 : 02:33:41

Great Thanks Jim!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources