在T-SQL中修剪不同长度的字符串中的字母数字字符

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

我有一个结果集,我想修剪一个2位数的后缀。字符串将始终具有不同的长度,但后缀将始终是由“ - ”分隔的两位数字。

Example:

APPTR-W302-01
NRSB-8920-09

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

现在我正在使用以下内容。这是一个黑客,因为'20'参数是任意的。

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

Is there a better way?

有没有更好的办法?

3 个解决方案

#1

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

后缀总是' - ##'吗?如果后缀长度没有改变,

Left(COURSENAME,LEN(COURSENAME)-3)

#2

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

以下代码显示了三种在T-SQL中功能相同的方法。恕我直言,“左”方法是最可读的。

   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))

#3

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

#1