Find Match Count

is there anyway i can find match count in a field record. for instance:
SELECT CountText(Content, "@.") AS Found FROM tb_Messages
which would return something like this:
RecordID Found
-- --
1 3
2 8
3 10
4 30
4 records affected.
where:
Content is the column containing some text.
@. is a character that i am searching for.
Found is total match found
tb_Messages is the table
and CountText is the supposed founction that counts the occurrence.
Anyhelp would be appreciated.
TascienWrite a user defined function, that takes the parameters you detailed below.
The udf should contain a loop, use the string functions CHARINDEX and
SUBSTRING, and return the count.
<tascienu@.ecoaches.com> wrote in message
news:1133131413.601235.276130@.z14g2000cwz.googlegroups.com...
> is there anyway i can find match count in a field record. for instance:
> SELECT CountText(Content, "@.") AS Found FROM tb_Messages
> which would return something like this:
> RecordID Found
> -- --
> 1 3
> 2 8
> 3 10
> 4 30
> 4 records affected.
> where:
> Content is the column containing some text.
> @. is a character that i am searching for.
> Found is total match found
> tb_Messages is the table
> and CountText is the supposed founction that counts the occurrence.
> Anyhelp would be appreciated.
> Tascien
>|||(tascienu@.ecoaches.com) writes:
> is there anyway i can find match count in a field record. for instance:
> SELECT CountText(Content, "@.") AS Found FROM tb_Messages
> which would return something like this:
> RecordID Found
> -- --
> 1 3
> 2 8
> 3 10
> 4 30
> 4 records affected.
> where:
> Content is the column containing some text.
> @. is a character that i am searching for.
> Found is total match found
> tb_Messages is the table
> and CountText is the supposed founction that counts the occurrence.
This should work:
SELECT CountText = datalength(Content) -
datalength(Replace(Content, '@.', '')))
FROM tb_MEssages
If your columns are declared as nvarchar/nchar/ntext, you need to divide
result by 2, as datalength returns the number of bytes.
(Some people might want to use len() here, but that will not fly.
Using len() we would return a incorrect result for a string like:
'This is @. a test string @.'
as len() does not count trailing blanks.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On 27 Nov 2005 14:43:33 -0800, tascienu@.ecoaches.com wrote:

>is there anyway i can find match count in a field record. for instance:
>SELECT CountText(Content, "@.") AS Found FROM tb_Messages
>which would return something like this:
>RecordID Found
>-- --
>1 3
>2 8
>3 10
>4 30
>4 records affected.
>
(snip)
Hi Tascien,
Try this:
SELECT RecordID,
LEN(Content) - LEN(REPLACE(Content, '@.', '')) AS Found
FROM tb_Messages
(untested - see www.aspfaq.com/5006 if you prefer a tested reply, or if
I misunderstood your requirements)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Sun, 27 Nov 2005 23:02:24 +0000 (UTC), Erland Sommarskog wrote:
(snip)
>(Some people might want to use len() here, but that will not fly.
>Using len() we would return a incorrect result for a string like:
> 'This is @. a test string @.'
>as len() does not count trailing blanks.)
Hi Erland,
This is the first time that an error in one of my post is corrected even
before I managed to post it. You're really quick today!!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Wonderful. Yes, these suggestions really work... I think I am going
with DataLength.
Thanks everyone...
T.