How to extract value from middle of substring in SQL?

I have column called “CustomerId” with value “1_Nissan_028” and “2_Ferrari_035”.

I would like to extract “Nissan” or “Ferrari” as “CustomerName”. CustomerName is located in middle of CustomerId and lenght varies.

Following SQL query return values like “Nissan_” or “Ferrar”.

How to write SQL statement?

SELECT cast(
        SUBSTRING(
        CustomerId,
        6,
        charindex('_', CustomerId)
        ) as nvarchar(32)
    ) as CustomerName
  
FROM [sales].[CustomerSales] 

Answer

Assuming that the value is always the 2nd delimited value, you can use STRING_SPLIT and its ordinal column to achieve this:

SELECT SS.value AS CustomerName
FROM sales.CustomerSales CS
     CROSS APPLY STRING_SPLIT(CS.CustomerId,'_',1) SS
WHERE SS.ordinal = 2;