Just another option using JSON

Example

Declare @YourTable Table ([SomeCol] varchar(50))  Insert Into @YourTable Values 
 ('Make\Model\Trim\New')
 
Select A.[SomeCol]
       ,Pos1 = JSON_VALUE(JS,'$[0]')
       ,Pos2 = JSON_VALUE(JS,'$[1]')
       ,Pos3 = JSON_VALUE(JS,'$[2]')
       ,Pos4 = JSON_VALUE(JS,'$[3]')
 From  @YourTable A
Cross Apply (values ('["'+replace(string_escape([SomeCol],'json'),'\\','","')+'"]') ) B(JS)

Results

SomeCol               Pos1  Pos2    Pos3    Pos4
Make\Model\Trim\New   Make  Model   Trim    New

Note: I had to escape the backslash, thus the \\

IF FOUR elements (and no more), you can use parsename()

Select A.[SomeCol]
       ,Pos3 = parsename(replace(SomeCol,'\','.'),2)
 From  @YourTable A

EDIT - As pointed out in the comments, this will only work with SQL Server 2022 or Azure.

You could do something still pretty ugly with string_split:

select
reverse(value) from (
select * from
STRING_SPLIT(reverse('Make\Model\Trim\New'), '\',1)
)t where
ordinal = 2

Fiddle

You'd be a lot better off storing your data appropriately, as opposed to doing this kind of stuff every time you read the data.

So this is a bit of a hack (to say the least) but as long as your string isn't really long, and you have fewer than 4 delimiters, you can hack out the pieces using parsename() . parsename is a function designed for extracting parts of a fully qualified object name (e.g. server.database.table.schema or lower cardinality versions of the same).

The hack you can do is replace all the slashes in your string with periods, and then extract the names.

declare @str varchar(100) = 'make\model\trim\new'

select @str = replace(@str, '\', '.')
select
    p1 = parsename(@str, 1),
    p2 = parsename(@str, 2),
    p3 = parsename(@str, 3),
    p4 = parsename(@str, 4)

Be warned however, if any individual part is longer than 128 characters (i.e. the length of a sysname object) all parts will return null (At least I think that's how it works. truthfully, you might want to play around with different lengths of total strings and string parts if you're concerned about either being fairly long).

e.g., this works, but if you make one of the strings longer, it fails:

declare @str nvarchar(1000) = concat(replicate('a', 128), '\', replicate('b', 128), '\', replicate('c', 128), '\', replicate('d', 128))

select @str = replace(@str, '\', '.')
select len(@str)
select
    p1 = parsename(@str, 1),
    p2 = parsename(@str, 2),
    p3 = parsename(@str, 3),
    p4 = parsename(@str, 4)

I am late to the game, but here is an exact solution for your scenario.
I'd like to extract only the "Trim" from this string. Essentially, extract the substring between the last backslash and the second-to-last backslash.

It is using XML and XQuery, and will work on any version starting from SQL Server 2012 onwards.

Notable points:

• CROSS APPLY is tokenizing input as XML.
• Trim is retrieved based on a sequential position via XPath predicate: /root/r[last() - 1] .

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, CAR_DESC VARCHAR(100));  
INSERT INTO @tbl (CAR_DESC) VALUES 
('Make\Model\Trim\New'),
('Make\Model\Category\Trim\New'),
('Make\Model\Category\Sub-Category\Trim\New');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = '\';

SELECT t.* 
    , c.value('(/root/r[last() - 1]/text())[1]', 'VARCHAR(30)') AS [Trim]
FROM @tbl AS t
    CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' + 
        REPLACE(CAR_DESC, @separator, ']]></r><r><![CDATA[') + 
        ']]></r></root>' AS XML)) AS t1(c);

Output