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 < Len(@INPUTSTRING)
BEGIN
IF Charindex(@SEARCHSTRING, @INPUTSTRING, @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 < 2
BEGIN
PRINT ‘THE SEARCHED STRING APPEARED >> ‘
+ 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