CODEWITHSUNDEEP
sql-serverstringt-sqlsql-server-2012
Serdia
Serdia

Reputation: 4428

How to retrieve only particular part of string in SQL Server 2012

My data looks like that:

PrimISOcodeGL
99507 - Swimming Pools – installation & service – below ground - 1799 - Hazard 3
18501 - Supermarkets - 5411 - Hazard 1
91580 - Contractors Executive Supervisors  - 1541 - Hazard 2
10073 - Automobile Sales, Repair or Service Shop - 7539 - Hazard 2

How can I retrieve only the name between first and second "-"?

So it should be like that:

PrimISOcodeGL
Swimming Pools
Supermarkets
Contractors Executive Supervisors
Automobile Sales, Repair or Service Shop

I am trying to use function like CHARINDEX, LTRIM, RTRIM, LEN

Upvotes: 1

Views: 87

Answers (1)

S3S
S3S

Reputation: 25152

Here's a way using charindex and substring.

ONLINE DEMO

declare @table table (PrimISOcodeGL varchar(256))

insert into @table 
values
('99507 - Swimming Pools - installation & service - below ground - 1799 - Hazard 3'),
('18501 - Supermarkets - 5411 - Hazard 1'),
('91580 - Contractors Executive Supervisors  - 1541 - Hazard 2'),
('10073 - Automobile Sales, Repair or Service Shop - 7539 - Hazard 2')

select
    ltrim(substring(substring(PrimISOcodeGL,charindex('-',PrimISOcodeGL) + 1,9999),1,charindex('-',substring(PrimISOcodeGL,charindex('-',PrimISOcodeGL) + 1,9999)) - 1))
from
    @table

Upvotes: 2

Related Questions