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