Technical Article

Search a string in a text with number of occurrences

,

Copy and Paste the script in SSMS

Create the Procedure in your desired database

Run the procedure:

--EXEC Usp_findstring 'Write your input search string here', 'Search string'

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Usp_findstring')
DROP PROCEDURE Usp_findstring
GO

CREATE PROC Usp_findstring (@INPUTSTRING  VARCHAR(max), 
                           @SEARCHSTRING VARCHAR(500)) 
AS 
  BEGIN 
      DECLARE @OCCURENCES  AS INT, 
              @POSITION    AS INT, 
              @RETURNVALUE INT 

      SET @OCCURENCES = 0 
      SET @POSITION = 0 

      WHILE @POSITION  0 
              BEGIN 
                  SET @OCCURENCES = @OCCURENCES + 1 
                  SET @POSITION = Charindex(@SEARCHSTRING, @INPUTSTRING, 
                                  @POSITION 
                                  ) 

                  PRINT 'POSITION OF THE SEARCH STRING >> "' 
                        + @SEARCHSTRING + '" IS AT :' 
                        + Cast(@POSITION AS VARCHAR(200)) + Char(13) 
                        + Char(10) 
              END 

            SET @POSITION = @POSITION + 1 
        END 

      SET @RETURNVALUE = @OCCURENCES 

      IF @RETURNVALUE > 0 
         AND @RETURNVALUE > ' 
                  + Cast(@RETURNVALUE AS VARCHAR(200)) 
                  + ' TIME' 

            PRINT '**** END OF SEARCH ***' + Char(10) 
        END 
      ELSE IF @RETURNVALUE > 1 
        BEGIN 
            PRINT 'THE SEARCHED STRING APPEARED >> ' 
                  + Cast(@RETURNVALUE AS VARCHAR(200)) 
                  + ' TIMES' 

            PRINT '**** END OF SEARCH ***' + Char(10) 
        END 
      ELSE 
        BEGIN 
            PRINT 'search string : "' + @SEARCHSTRING 
                  + '" is not found' 

            PRINT '**** END OF SEARCH ***' + Char(10) 
        END 
  END

Rate

3.33 (3)

You rated this post out of 5. Change rating

Share

Share

Rate

3.33 (3)

You rated this post out of 5. Change rating