What is the simplest/best way to remove substrings at the end of a string?

I have a function that norma开发者_JAVA技巧lizes addresses. What I would like to do now is remove any of the strings in a limited, specified list if they occur at the end of the string. Let's say the strings I want to remove are 'st', 'ave', 'rd', 'dr', 'ct'... If the string ends with any of these strings, I want to remove them. What is the best way to accomplish this, using T-SQL (this will not be part of a select statement)?

Edit:

This is a function that accepts one address and formats it. I would like to inline the code, and the list, but in the simplest way possible. For example, some code that I've been playing with is:

if @address LIKE '%st'
 SET @address = substring(@address, 1, PatIndex('%st', @address) - 1)

Is this a good method? How can I put it in some sort of loop so I can repeat this code with different values (other than st)?

Adding the values to be trimmed to a new table allows you to

• easily add new values
• use this table to clean up adresses

SQL Statement

DECLARE @Input VARCHAR(32)
SET @Input = 'Streetsstaverddrad'

DECLARE @Trim TABLE (Value VARCHAR(32))

INSERT INTO @Trim
SELECT 'st'
UNION ALL SELECT 'ave'
UNION ALL SELECT 'rd'
UNION ALL SELECT 'dr'
UNION ALL SELECT 'ad'

WHILE EXISTS (
  SELECT  * 
  FROM    (
            SELECT  [Adres] = @Input
           ) i 
           INNER JOIN @Trim t ON i.Adres LIKE '%' + t.Value
  )
BEGIN
  SELECT  @Input = SUBSTRING(Adres, 1, LEN(Adres) - LEN(t.Value))
  FROM    (
            SELECT  [Adres] = @Input
          ) i
          INNER JOIN @Trim t ON i.Adres LIKE '%' + t.Value
END          

SELECT @Input

In SQL Server 2005 it is possible to define a user-function which enables regular expression matching. You will need to defined a function which strips the trailing strings. A RegEx to match the scenarios you mention would be something like...

\s+(ave|rd|dr|ct)\s*$