Technical Article

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

,

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 = ''

CREATE FUNCTION [dbo].[LSTR] (@str  VARCHAR(8000), @len INT)
RETURNS VARCHAR(8000)
AS
BEGIN
SET @str = LTRIM(RTRIM(@str))
RETURN(
CASE WHEN @len=0 OR LEN(@str)+@len <= 0
THEN ''
ELSE
CASE WHEN @len>0
THEN LEFT(@str, @len)
ELSE
LEFT(@str, LEN(@str)+@len)
END
END
)
END


CREATE FUNCTION [dbo].[RSTR] (@str VARCHAR(8000), @len INT)
RETURNS VARCHAR(8000)
AS
BEGIN
SET @str = LTRIM(RTRIM(@str))
RETURN(
CASE WHEN @len=0 OR LEN(@str)+@len <= 0
THEN ''
ELSE
CASE WHEN @len>0
THEN RIGHT(@str, @len)
ELSE
RIGHT(@str, LEN(@str)+@len)
END
END
)
END

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

1.67 (3)

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

1.67 (3)

You rated this post out of 5. Change rating

Related content

Technical Article

Write Reports from SQL to Disk (Even HTML!)

Ever need to write reports out to a folder? I have found that creating output files are SA-WEET! (and easy too) The sample script uses BCP to create an HTML file.This process works well for reports that need to be generated nightly and take too long to run in real time.  Use an SMTP mail […]

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2002-04-18

4,266 reads