Remove special characters from string SQL after character

Question

My current data set has a value that is acceptablevalue_5cjunk data. The data that I want to remove using SQL is the string that starts with _5c.

I have tried using a replace statement with a like function which does not work.

IF OBJECT_ID('mytable') IS NOT NULL drop table mytable
CREATE TABLE mytable
    ([mycol] varchar(50))
;

INSERT INTO mytable
    ([mycol])
VALUES
    ('97W'),
    ('125_5c0a9abac73d4'),
    ('121_5c0a9abac73d4'),
    ('121_5c0a9abac73d4'),
    ('123_5c0a9abac73d4'),
    ('112H'),
    ('111H')
;


SELECT REPLACE([mycol],like'_5c%','')
from mytable

What I would like to see is:

97W
125
121
121
123
112H
111H

Answer 1

There is no function that will replace after a certain pattern is matched, however, since you only want to remove the characters, replace is not necessary at all, you can just use LEFT to strip out the characters before the pattern (using CHARINDEX to find the pattern),along with a case expression to make sure you account for scenarios where the pattern is not found:

SELECT CASE WHEN CHARINDEX('_5c', MyCol) = 0 THEN MyCol 
            ELSE LEFT(MyCol, CHARINDEX('_5c', MyCol) - 1) 
        END
FROM mytable;