Use Column value as string pattern in REPLACE function

Question

I'm trying to strip off version suffixes (_v1...) from a whole bunch of app 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');

I could normally do this by nesting a lot of replace statements:

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

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

I wanted to create a CTE that stored all the prefixes and then REPLACE them all 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 this does not quite work. The multiple from statements gives me the cartesian product of both tables and only strips out the suffix on the rows where the value happens to line up.

Demo in Sql Fiddle

Note: I know I could convert this to a function, but I'd rather keep everything within a single query if possible.

+1 for trying a solution and the sqlfiddle

Lamak
– Lamak

2014-08-11 21:03:26 +00:00
Commented Aug 11, 2014 at 21:03 — Lamak
– Lamak, Commented Aug 11, 2014 at 21:03

Lamak · Accepted Answer · 2014-08-11 21:07:43Z

3

This does it:

;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

The results are:

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

And here is the modified sqlfiddle.

answered Aug 11, 2014 at 21:07

Lamak

70.8k12 gold badges119 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

KyleMit Over a year ago

Smart! Join the version rows only on instances where they match up against the end of the name so they're guaranteed to work in the replace function.

Lamak Over a year ago

@KyleMit In any case, my answer is simplistic since it assumes that there is no other coincidence of that suffix in the name. If you had a name that was: MyApp_v2_ThisIsTheOne_v2, then it will return MyApp_ThisIsTheOne.

Collectives™ on Stack Overflow

Use Column value as string pattern in REPLACE function

Demo in Sql Fiddle

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related