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

One 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.

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.

There's only crap on TV, so...
http://milambda.blogspot.com/2006/02/more-fun-with-strings.html


ML

---
http://milambda.blogspot.com/

ML,

Good job, but that dark blue background ! Do something about it, would you
please?

I like it dark. :)
I plan to rethink the layout when I move the blog to my own CMS...


ML

---
http://milambda.blogspot.com/

laurenq uantrell wrote:
DECLARE @str VARCHAR(1000)
SET @str= 'Lamb'

SELECT LEN(REPLACE(stringdata,@str,@str+'_'))-LEN(stringdata)
FROM tbltable;

Thanks David and all other posters for these solutions.
lq

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

Show quoteHide quote

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' ;

On 16 Feb 2006 14:11:22 -0800, laurenq uantrell wrote:

Hi laurenq,

SELECT ( DATALENGTH(StringData)
       - DATALENGTH(REPLACE(StringData, 'Lamb', '')) )
     / DATALENGTH('Lamb')
FROM   tblTable

Who has the highest bid?
Need Help Please...
How to compare strings for equality in Transact SQL
Why does doing update in chunks works faster than doing the whole?
Unique index on joined view for fulltext index