Monday, September 26, 2011

Trim Leading Or Trailing Control Characters

DECLARE @MyString VARCHAR(1000)
--Control character

DECLARE @Tab            VARCHAR(2)
DECLARE @LineFeed       VARCHAR(2)
DECLARE @CarriageReturn VARCHAR(2)
DECLARE @SearchPattern  VARCHAR(10)

SET @Tab            =              CHAR(9)
SET @LineFeed       =              CHAR(10)
SET @CarriageReturn =              CHAR(13)

--Search for any single of more chars not with specified range
SET @SearchPattern = '%[^ '   + @Tab + @LineFeed + @CarriageReturn + ']%'

SET @MyString = CHAR(10) + ' ' + CHAR(10)+ CHAR(9) + 'My name is ' + CHAR(10) 
				+ ' babu ' + CHAR(10) + ' ' + CHAR(10) + CHAR(10) + ' ' + CHAR(10)

SELECT LEN(@MyString) AS TotalLength
SELECT @MyString AS MyString

SELECT LEN( 
RTRIM( LTRIM( 
	SUBSTRING( @MyString, 
		PATINDEX(@SearchPattern, @MyString), 
		LEN(@MyString) - PATINDEX(@SearchPattern, @MyString) - PATINDEX(@SearchPattern, 
		REVERSE(@MyString)) + 2
		) 
	) ) 
) AS AfterTrimedLength

SELECT 
RTRIM( LTRIM( 
	SUBSTRING( @MyString, 
		PATINDEX(@SearchPattern, @MyString), 
		LEN(@MyString) - PATINDEX(@SearchPattern, @MyString) - PATINDEX(@SearchPattern, 
		REVERSE(@MyString)) + 2
		) 
	) ) AS AfterTrimed

No comments: