1

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

Improve this question
1
  • 1
    Find first charindex. Take string from there + 1 till charindex of next hyphen. So, that will be substring with numbers passed in as charindex. Commented Oct 5, 2017 at 19:45

1 Answer 1

Reset to default
2

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
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Thanks for such fast respond. But this one doesnt stop after second '-'. As a result I need just Swimming Pools
Why use 9999 here?
Yes it does @Oleg see the online demo
@Amit arbritray value large enough to encompass the rest of the string regardless of how big it is (relatively). Basically a lazy and cheap way instead of using Len()
Awesome, never see this way before. Thank you very much!!
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.