How to extract value from middle of substring in SQL?-CodePudding

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]

CodePudding user response：

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;

CodePudding user response：

An alternative quirky way of doing this would be to use translate

with customersales as (
  select '1_Nissan_028' CustomerId union select '2_Ferrari_035'
)
select customerId,
  Replace(Translate(customerId, '0123456789','__________'),'_','') CustomerName
from customersales;