Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

LSTR() and RSTR(): Enhanced LEFT() and RIGHT()

By R. van Laake, 2013/05/21

Quite often I need to cut off the first (or last) few characters of a string. To do just that I created two functions LSTR() and RSTR().

Syntax: LSTR( @stringtocut, @length )
Note: @stringtocut is trimmed in the function

When @length is equal or larger than zero, this function performs the same as LEFT( LTRIM(RTRIM(@stringtocut)), @length ).
When @length is smaller than zero, it returns LTRIM(RTRIM(@stringtocut)) minus the right @length characters. 

Examples:
SET @ret = LSTR(' abcdef ',4) -> @ret = 'abcd'
SET @ret = LSTR(' abcdef ',0) -> @ret = ''
SET @ret = LSTR(' abcdef ',-4) -> @ret = 'ab'
SET @ret = LSTR(' abcdef ',9) -> @ret = 'abcdef'
SET @ret = LSTR(' abcdef ',-9) -> @ret = ''


Syntax: RSTR( @stringtocut, @length )
Examples:
SET @ret = RSTR(' abcdef ',4) -> @ret = 'cdef'
SET @ret = RSTR(' abcdef ',0) -> @ret = ''
SET @ret = RSTR(' abcdef ',-4) -> @ret = 'ef'
SET @ret = RSTR(' abcdef ',9) -> @ret = 'abcdef'
SET @ret = RSTR(' abcdef ',-9) -> @ret = ''

Total article views: 660 | Views in the last 30 days: 9
 
Related Articles
FORUM

TSQL error: 'Invalid length parameter passed to the substring function.'

TSQL error: 'Invalid length parameter passed to the substring function.'

FORUM

Max length allowed for RETURN in a function

Max allowed length for value returned using RETURN in a user defined function

FORUM

Invalid length parameter passed to the SUBSTRING function.

Msg 536, Level 16, State 5, Procedure TRA_BrokenSalesPROCV2, Line 194 Invalid length parameter passe...

FORUM

Record Length

Record Length of a Float data type

Tags
miscellaneous    
sqlinsider scripts    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones