Use column value as row template in REPLACE function

Question

I am trying to disable version suffixes ( _v1...) from an entire group of application names.

For example, let's say I have the following data:

CREATE TABLE applications 
 (
   name varchar(20)
 );

INSERT INTO applications
 (name)
VALUES
 ('MyApp_v2'),
 ('MyApp_v1'),
 ('MyApp'),
 ('YourApp_R1');

Normally, I could do this by inserting a lot of replacement statements:

SELECT REPLACE(REPLACE(REPLACE(
         name,
       '_R1', ''),
       '_v2', ''),
       '_v1', '')
       As AppNameWithoutSuffix
FROM applications

But I have many version numbers to check, so I was hoping for something cleaner. Based on the following questions:

• SQL Nested Replace
• Are multiple Replace statements a bad practice / is there another way to write them?

I wanted to create a CTE that saved all the prefixes, and then REPLACEall of them like this:

;WITH versions (suffix) AS (
    SELECT '_R1' UNION ALL
    SELECT '_v2' UNION ALL
    SELECT '_v1'
)
SELECT REPLACE(a.name, v.suffix, '') As AppNameWithoutSuffix
FROM applications a,
     versions v

But it doesn’t quite work. A lot of the operators give me the Cartesian product of both tables and only supersedes the suffix in the rows where the value matters in order to line up.

Demo in Sql Fiddle

. , , , .

Answer 1

:

;WITH versions (suffix) AS (
    SELECT '_R1' UNION ALL
    SELECT '_v2' UNION ALL
    SELECT '_v1'
)
SELECT  name, 
        REPLACE(A.name,ISNULL(B.suffix,''),'') VersionlessName
FROM applications A
LEFT JOIN versions B
    ON A.name LIKE '%'+B.suffix

:

╔════════════╦═════════════════╗
║    name    ║ VersionlessName ║
╠════════════╬═════════════════╣
║ MyApp_v2   ║ MyApp           ║
║ MyApp_v1   ║ MyApp           ║
║ MyApp      ║ MyApp           ║
║ YourApp_R1 ║ YourApp         ║
╚════════════╩═════════════════╝

sqlfiddle.