Trimming alphanumeric characters from a strings of varying lengths in T-SQL

Question

I have a result set that I want to trim a 2 digit suffix from. The strings will always be of varying lengths, but the suffixes will always be two digits separated by '-'.

Example:

APPTR-W302-01
NRSB-8920-09

Right now I am using the following. This is a hack because the '20' parameter is arbitrary.

REVERSE(SUBSTRING(REVERSE(COURSENAME),4,20))

Is there a better way?

Answer 1

Will the suffix always be '-##' ? If the suffix length doesn't change,

Left(COURSENAME,LEN(COURSENAME)-3)

Answer 2

declare @t varchar(30)
set @t='1234-5678-99'
select right(@t, 2)

Answer 3

The following code shows three methods that are functionally equivalent in T-SQL. IMHO the "LEFT" method is the most readable.

   DECLARE @courseName VARCHAR(20)
   SET @courseName = 'APPTR-W302-01' -- we need to trim the trailing 2 digits and dash

   SELECT 
        SUBSTRING(@courseName, 1, LEN(@courseName) - 3), 
        LEFT(@courseName, LEN(@courseName) - 3), 
        REVERSE(SUBSTRING(REVERSE(@courseName),4,20))