How to get string between two characters

Question

I need to get the string between the - and the ·, which is the GigabitEthernet1/0/1

CW-3D13-SW1 - GigabitEthernet1/0/1 · Uplink to 1K5-Core1
CW-3D13-SW1 - FastEthernet1/0/43 · PHSA-MPAACT-3D13/16 - Cisco 2811 Fa 0/0
c&w-internet-sw-ACB - GigabitEthernet1/0/24 · MPAACT PNG/UBC School of Medicine
c&w-internet-sw-ACB - GigabitEthernet1/0/25 - Int-Link-CW-BCCA-Oak-St

I can use the following

SUBSTRING(Caption, CHARINDEX(' - ', Caption) + 3, CHARINDEX(' · ', Caption) - CHARINDEX('- ', Caption) + LEN(' · ') - 3)

And it would give me what I want, but there are some instance where the · is replace by the -. For example:

c&w-internet-sw-ACB - GigabitEthernet1/0/25 - Int-Link-CW-BCCA-Oak-St

And this would break my query. Is there a way to satisfy both - and the ·

Thank

Update - Is it possible to add another condition? When string are as bellow:

VPN 3030  - PCI Fast Ethernet  

There is no - or · at the end. In this case I just want what ever is after the -

Answer 1

Try this

Declare @LeftMarker varchar(3)
Declare @RightMarker varchar(3)
    Set @LeftMarker = ' - '     --<--- Replace this with your choice
    Set @RightMarker = ' · '    --<--- Replace this with your choice

Declare @LMarkerLen int
    Set @LMarkerLen = LEN(@LeftMarker)

SELECT  Case When CHARINDEX(@RightMarker, Caption) > 0 Then
            SUBSTRING(Caption, CHARINDEX(@LeftMarker, Caption) + @LMarkerLen, CHARINDEX(@RightMarker, Caption) - (CHARINDEX(@LeftMarker, Caption) + @LMarkerLen))
        Else
            SUBSTRING(Caption, CHARINDEX(@LeftMarker, Caption) + @LMarkerLen, CHARINDEX(@LeftMarker, Caption, CHARINDEX(@LeftMarker, Caption) + @LMarkerLen) - (CHARINDEX(@LeftMarker, Caption) + @LMarkerLen))
        End AS Result
FROM .......