You could create a calculated, persisted column in your table definition and SELECT that instead.

And mine didn't? For the sample data you provided, I think it did; it's fast too.

If, as Scott no doubt correctly surmised, your actual data contains variations on...

Really? >8,000 points and you still cross-post!

Answers here, please.

DROP TABLE IF EXISTS #Path;

CREATE TABLE #Path
(
    FilePath VARCHAR(200) NOT NULL
);

INSERT #Path
(
    FilePath
)
VALUES
('A:\AABCD\EDFG\RFG\MyDB123.bak')
,('A:\RFG\MyDB1123.bak')
,('A:\AABCD\EDFG\MyDB1223.bak');

SELECT p.FilePath
      ,LEFT(p.FilePath, CHARINDEX('MyDB',...

1. Then replication or backup/restore would seem reasonable options.
2. Probably. It would require some analysis and design work to decide on how best to handle all the changes, and in...

OK, now I understand why you wrote that.

Despite mention of the CS by the OP, my assumption was that this was a data flow containing multiple columns, one of which...

Are there lots of different tables being changed? If so, the answer is likely to be one of

1. Replication
2. Backup / Restore

If only a handful of table are being modified,...

I assume all you need it this:

LEN((DT_WSTR, 4) [Your Column]) == 4

If the values can also be longer than 4 characters as well increase the...

I understand, but most SQL devs (including me) try to avoid triggers, as they can become difficult to manage, and sometimes have unexpected side effects.

The above is such a complicated...

This sounds like business logic which should be handled in the stored proc which your front-end application calls when performing the INSERT.

Which is quite handy on those occasions where the...

I am looking to see if there are any ways to improve the run time for select queries.

This was the request I responded to. The remainder of your post provides...

Take a look in the Books section here too.

SELECT * FROM TABLE can't really be optimised, as far as I am aware. It's a full table scan.

(Even though you want all the columns, you should still name them,...

I suggest that you do it using this method (copied from https://sqlperformance.com/2020/09/locking/upsert-anti-pattern):

BEGIN TRANSACTION;
 
UPDATE dbo.t WITH (UPDLOCK, SERIALIZABLE) SET val = @val WHERE  = @key;
...