Hi,
Here is the another version,
CREATE PROCEDURE [dbo].[usp_GetLengthyString]
(
@sInputString VARCHAR(8000)
) AS
BEGIN
DECLARE @sItem VARCHAR(8000)
DECLARE @lengthyString VARCHAR(8000)
DECLARE @sDelimiter VARCHAR
SET @sDelimiter = ' '
DECLARE @List TABLE (string VARCHAR(8000), leng INT)
WHILE CHARINDEX(@sDelimiter,@sInputString,0) <> 0
BEGIN
SELECT @sItem=RTRIM(LTRIM(SUBSTRING(@sInputString,1,CHARINDEX(@sDelimiter,@sInputString,0)-1))),
@sInputString=RTRIM(LTRIM(SUBSTRING(@sInputString,CHARINDEX(@sDelimiter,@sInputString,0)+LEN(@sDelimiter),LEN(@sInputString))))
IF LEN(@sItem) > 0
INSERT INTO @List
SELECT @sItem, LEN(@sItem)
END
IF LEN(@sInputString) > 0
INSERT INTO @List
SELECT @sInputString, LEN(@sInputString)
SELECT @lengthyString = string FROM @List ORDER BY leng ASC
PRINT 'The longest sub string is ' + @lengthyString
END