Count Occurances in a column

Is there a way to count the number of times a character or string
appears in a column in a table row/column?
Example, tblTable has column StringData varchar(1000)
StringData contains the value "Mary Had A Little Lamb Lamb and Then It
Died"
I want to know how many times "Lamb" appears in this string.
Any help is appreciated.
ThanksOne popular way to do this is:
SELECT ( LEN( stringdata ) -
LEN( REPLACE( stringdata, 'Lamb', '' ) ) ) / LEN( 'Lamb' )
FROM tbl ;
Alternatively, you can use a table of sequentially incrementing numbers and
construct a generic logic using SUBSTRING functions too.
Anith|||laurenq uantrell wrote:
> Is there a way to count the number of times a character or string
> appears in a column in a table row/column?
> Example, tblTable has column StringData varchar(1000)
> StringData contains the value "Mary Had A Little Lamb Lamb and Then It
> Died"
> I want to know how many times "Lamb" appears in this string.
> Any help is appreciated.
> Thanks
DECLARE @.str VARCHAR(1000)
SET @.str= 'Lamb'
SELECT LEN(REPLACE(stringdata,@.str,@.str+'_'))-LEN(stringdata)
FROM tbltable;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||LEN has its problems, so here's one with DATALENGTH:
declare @.text nvarchar(4000)
declare @.string nvarchar(4000)
set @.text = N'Mary Had A Little Lamb Lamb and Then It Died'
set @.string = N'Lamb'
select (datalength(@.text) - datalength(replace(@.text, @.string, N''))) /
datalength(@.string)
ML
http://milambda.blogspot.com/|||Yup, that is right.
Anith|||DECLARE @.StringData varchar(1000)
DECLARE @.counter int
SET @.StringData = 'Mary Had A Little Lamb Lamb and Then It Died'
SET @.counter = 0
WHILE PATINDEX('%Lamb%',@.StringData) <> 0
BEGIN
SET @.counter = @.counter + 1
SET @.StringData = STUFF(@.StringData, PATINDEX('%Lamb%',@.StringData), 4, '')
END
PRINT @.counter
If you want to do this for all rows in a table, one way to accomplish this
is to put the above into a (gasp!) cursor:
DECLARE @.pkcol int --Change this to match that datatype of your PK column
DECLARE @.StringData varchar(1000)
DECLARE @.counter int
CREATE TABLE #results (pkcol int, StringCount int) --same here
DECLARE my_cursor CURSOR STATIC FORWARD_ONLY
FOR
SELECT pkcol, StringData FROM tblTable
FETCH NEXT FROM my_cursor INTO @.pkcol, @.StringData
WHILE (@.@.FETCH_STATUS = 0 )
BEGIN
SET @.counter = 0
WHILE PATINDEX('%Lamb%',@.StringData) <> 0
BEGIN
SET @.counter = @.counter + 1
SET @.StringData = STUFF(@.StringData, PATINDEX('%Lamb%',@.StringData), 4,
'')
END
INSERT INTO #results VALUES (@.pkcol, @.counter)
FETCH NEXT FROM my_cursor INTO @.pkcol, @.StringData
END
SELECT pkcol, StringCount FROM #results
"laurenq uantrell" wrote:

> Is there a way to count the number of times a character or string
> appears in a column in a table row/column?
> Example, tblTable has column StringData varchar(1000)
> StringData contains the value "Mary Had A Little Lamb Lamb and Then It
> Died"
> I want to know how many times "Lamb" appears in this string.
> Any help is appreciated.
> Thanks
>|||Mark,
A single SELECT with a table of numbers may do better than a cursor :
SELECT COUNT(*)
FROM Nbrs
WHERE SUBSTRING( @.stringdata, n, LEN( 'Lamb' ) ) = 'Lamb' ;
Anith|||There's only crap on TV, so...
http://milambda.blogspot.com/2006/0...th-strings.html
ML
http://milambda.blogspot.com/|||On 16 Feb 2006 14:11:22 -0800, laurenq uantrell wrote:

>Is there a way to count the number of times a character or string
>appears in a column in a table row/column?
>Example, tblTable has column StringData varchar(1000)
>StringData contains the value "Mary Had A Little Lamb Lamb and Then It
>Died"
>I want to know how many times "Lamb" appears in this string.
>Any help is appreciated.
>Thanks
Hi laurenq,
SELECT ( DATALENGTH(StringData)
- DATALENGTH(REPLACE(StringData, 'Lamb', '')) )
/ DATALENGTH('Lamb')
FROM tblTable
Hugo Kornelis, SQL Server MVP|||Thanks David and all other posters for these solutions.
lq

count 2 value in a table

hi all pls kindly help
need to know how to do a count for 2 value in a column

example :
my table(entries)
has the field name 'selection'
under this selection, data inside have is e.g(apple,apple,pear,pear,pear,orange)
i need to do a COUNT on how many apple and pear there is inside this table

but i seriously have no idea, pls kindly help thanksThis sounds like homework from an "Introduction to databases" course, but I'm willing to give you the benefit of the doubt...SELECT Count(*), selection
FROM entries
GROUP BY selection
ORDER BY Count(*) DESC -- gratis-PatP|||SELECT Count(*), selection
FROM entries
GROUP BY selection
ORDER BY Count(*) DESC -- gratis

sorry for my ignorance..
wat u meant by DESC over here??

wat i want is to count how many pears and apple there is in that table but not orange|||DESC means descending, and you don't have to use it if you don't want to

did you want the total of apples and pears? that's different --

SELECT count(*) as applesandpears
FROM entries
WHERE selection in ('apple','pear')|||thanks so much...
really so basic

need to read up my books again :(|||Sorry, my background is showing :rolleyes:

Gratis is a Latin vulgate term that means "free of charge" or "complimentary". When I used it in a comment, I meant that I expected it to make things easier to use, without actually impacting the solution at all.

As Rudy pointed out, in this case it simply orders the result set so that the most frequently occuring items appear at the top of the list. You can safely ignore it if it doesn't help you.

-PatP|||ok got it ..
but still appreciated
:)