Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

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

By R. van Laake,

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: 696 | Views in the last 30 days: 8
 
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...

SCRIPT

Calculate Length - Most Advanced UOC

This function is used to calculate the actual unit of conversion for length.

FORUM

Record Length

Record Length of a Float data type

Tags
miscellaneous    
sqlinsider scripts    
t-sql    
 
Contribute