|
|
|
|
Allows you to count how many times a string appears within another string. example - count carriage returns within text. Handles up to 8K characters. Tricks can get around 8K limit for large text fields.
|
| |
|
code:
Can't Copy and Paste this?
Click here for a copy-and-paste friendly version of this code!
Terms of Agreement:
By using this code, you agree to the following terms...
- You may use
this code in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.
- You MAY NOT redistribute this code (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.
- You may link to this code from another website, but ONLY if it is not wrapped in a frame.
- You will abide by any additional copyright restrictions which the author may have placed in the code or code's description.
|
--**************************************
-- Name: Count Matches
-- Description:Allows you to count how many times a string appears within another string. example - count carriage returns within text. Handles up to 8K characters. Tricks can get around 8K limit for large text fields.
-- By: Lewis E. Moten III
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=415&lngWId=5--for details.--**************************************
CREATE FUNCTION [dbo].[funCountMatch] (@Text varchar(8000), @Pattern varchar(20))
RETURNS INT
AS
BEGIN
DECLARE @Matches INT
DECLARE @Index INT
DECLARE @MaxIndex INT
DECLARE @PatternLen INT
SET @PatternLen = Len(@Pattern + '.') - 1
SET @MaxIndex = Len(@Text) - (@PatternLen - 1)
SET @Index = 1
SET @Matches = 0
WHILE @Index < @MaxIndex
BEGIN
IF SUBSTRING(@Text, @Index, @PatternLen) = @Pattern SET @Matches = @Matches + 1
SET @Index = @Index + 1
END
RETURN(@Matches)
END
|
|
Other 31 submission(s) by this author
|
|
Report Bad Submission
|
Your Vote
|
| |
Other User Comments
|
 2/23/2002 8:57:58 AM: alex
Better for perfomance :
CREATE FUNCTION [dbo].[funCountMatch] (@Text varchar(8000), @Pattern varchar(20))
RETURNS INT
AS
BEGIN
DECLARE @Matches INT
DECLARE @Index INT
DECLARE @MaxIndex INT
DECLARE @PatternLen INT
SET @PatternLen = Len(@Pattern + '.') - 1
SET @MaxIndex = Len(@Text) - (@PatternLen - 1)
SET @Index = 1
SET @Matches = 0
WHILE @Index < @MaxIndex
BEGIN
IF SUBSTRING(@Text, @Index, @PatternLen) = @Pattern
BEGIN
SET @Matches = @Matches + 1
SET @Index=@Index+@PatternLen-1
END
ELSE
SET @Index = @Index + 1
END
RETURN(@Matches)
END
(If this comment was disrespectful, please report it.)
|
2/23/2002 9:18:35 AM: korolchenko alexei
I think that look better:
SET @Matches=(LEN(@Text)-LEN(REPLACE(@Text,@Pattern,'')))/(LEN(@Pattern))
(If this comment was disrespectful, please report it.)
|
3/20/2003 2:13:17 AM: miracl
what s the trick to get around the 8k limit and use that with text field ?
(If this comment was disrespectful, please report it.)
|
3/20/2003 4:27:11 AM:
Just one Remark :)
However korolchenko alexei method is the simplest and i personally use the same method, but you need to keep in mind that in some cases all described methods can produce different results:
Example:
@Text='TATATA', @Pattern='TATA'
Result using 1 method: @Matches=2
Result using 2 method: @Matches=1
Result using 3 method: @Matches=1
(If this comment was disrespectful, please report it.)
|
Add Your Feedback
Your feedback will be posted below and an email sent to
the author. Please remember that the author was kind enough to
share this with you, so any criticisms must be stated politely, or they
will be deleted. (For feedback not related to this particular code, please
click here instead.)
To post feedback, first please login.
|
