Improving SQL Substring Extraction

I have a working version of an extraction step, that seems quite lengthy and I was curious if there's a simpler approach that I may be missing. I've seen SUBSTRING_INDEX() referenced in other questions on the forum, however, that function isn't recognized in my version of Sql Server Management Studio.

I have a string that looks like this: Make\Model\Trim\New

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.

Because the string length and number of backslashes may vary, I can't simply say "After the second and third backslashes". Below is my currently working code.

Select
 REVERSE(SUBSTRING(
     REVERSE(CAR_DESC),
     CHARINDEX('\',REVERSE(CAR_DESC))+1,
     CHARINDEX('\',REVERSE(CAR_DESC),CHARINDEX('\',REVERSE(CAR_DESC))+1)-CHARINDEX('\',REVERSE(CAR_DESC))-1
     )) AS TEST1
FROM TABLEA

Solution

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