DECLARE @TestStr varchar( 8000 )
SET @TestStr = 'This is a way to find occurrences of a string or character in a string.' +
               'This demonstrates how you can use the built-in string functions to solve most ' +
               'problems.'
-- To find out occurrences of 'string'
SELECT ( LEN( @TestStr ) - LEN( REPLACE( @TestStr , 'string' , '' ) ) ) / LEN( 'string' ) AS #Strings
/*
#Strings
--------
       3
*/
-- To find occurrences of a single-character like 'a'
SELECT LEN( @TestStr ) - LEN( REPLACE( @TestStr , 'a' , '' ) ) AS #Char
/*
#Char
-----
    8
*/
GO

-- Examples of the same technique:
/*
        The problem is to find the number of values in the delimited string.
*/
DECLARE @TextString varchar(8000)
SELECT @TextString = '12,34,56,78,90'
-- This is important to make the search easier
SELECT @TextString = '12,34,56,78,90' + ','
SELECT LEN( @TextString ) - LEN( REPLACE( @TextString , ',' , '' )) AS #Values
/*
#Values
-------
      5
*/
GO

/*
        The problem is to find the position of the delimiter after which a
        particular search value is found.
*/
DECLARE @TextString varchar(8000) , @SearchFor int
SELECT @TextString = '12,34,56,78,90' , @SearchFor = 78
SELECT LEN( @TextString ) - LEN( REPLACE( @TextString , ',' , '' )) + 1 AS #Values
SELECT LEN( NewStr ) - LEN( REPLACE( NewStr , ',' , '' ) ) AS Pos
FROM (
        SELECT LEFT( ',' + @TextString + ',' , 
                        CHARINDEX( ',' + CAST( @SearchFor AS varchar ) + ',' ,
                        ',' + @TextString + ',' ) )
) AS s( NewStr )
/*
Pos
---
  4
*/
GO

This page was last updated on May 01, 2006 04:28 PM.