Count Number of Occurrences of One String With Another

Posted: November 21, 2011 in SQL Server 2005

If you need a caculate age at a given date, you can try the following:

-- Declare variables
DECLARE @lv_check    VARCHAR (MAX);
DECLARE @lv_check_in VARCHAR (MAX);
DECLARE @li_count    INT;
DECLARE @li_pos      INT;
DECLARE @li_len      INT;

-- Initialise variables
SET @lv_check    = 'test';
SET @lv_check_in = 'Test that something is found in this test string.';
     
SET @li_count = 0;
SET @li_len   = LEN(@lv_check_in);
SET @li_pos   = CHARINDEX (@lv_check, @lv_check_in);

WHILE @li_pos > 0 AND @li_pos <= @li_len
BEGIN
   SET @li_count = @li_count + 1;
   SET @li_pos   = @li_pos   + 1;

   IF @li_pos < @li_len
   BEGIN
      SELECT @li_pos = CHARINDEX (@lv_check, @lv_check_in, @li_pos);
   END;
   ELSE
   BEGIN
      SELECT @li_pos = 0;
   END;
END;

SELECT @li_count;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s