CODEWITHSUNDEEP
sqlazure-sql-database
Kenny_I
Kenny_I

Reputation: 2523

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]

Upvotes: 0

Views: 139

Answers (2)

Stu
Stu

Reputation: 32614

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;

Upvotes: 1

Thom A
Thom A

Reputation: 96055

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;

Upvotes: 1

Related Questions