How to get string between two characters

问题:

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 -

回答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 .......

回答2:

It's ugly as hell, but I think it does what you need.

SELECT 
  SUBSTRING(Caption, 
            CHARINDEX(' - ', Caption) + 3, 
            CHARINDEX(' · ', Caption) - CHARINDEX('- ', Caption) + LEN(' · ') - 3) 
FROM
  Foo
WHERE
  CHARINDEX(' · ', Caption) > 0
UNION
SELECT
SUBSTRING(
  REVERSE(
  SUBSTRING(REVERSE(Caption), 
            CHARINDEX(' - ', REVERSE(Caption)) + 3,
            LEN(Caption))
  ),
  CHARINDEX(' - ', REVERSE(
  SUBSTRING(REVERSE(Caption), 
            CHARINDEX(' - ', REVERSE(Caption)) + 3,
            LEN(Caption))
  )) + 3,
  LEN(Caption))
FROM
  Foo
WHERE
  CHARINDEX(' · ', Caption) = 0

To briefly explain, for those where the dot is not a separator/delimiter, reverse the caption and get a substring from the first instance of " - " all the way to the end. Reverse the result of this operation, returning it to the order it was originally and again get the substring from the first instance of " - " to the end.

回答3:

I created a min function for this.

create function dbo.fnMin( @P1 int, @P2 int)
    returns int
as
begin
    return case when @P1 > @P2 then coalesce(@P2,@P1) else coalesce(@P1,@P2) end;
end
go

It just returns the smaller of the two values. Then you can use it to do a RIGHT followed by a LEFT. You can actually put this in a single select statement but splitting it the way I did makes it more readable, in my opinion. Give it a try... hope it works!

select coalesce(left(SubStr, dbo.fnMin(nullif(charindex(' - ',SubStr),0),nullif(charindex(' · ',SubStr),0)) ),SubStr)
from (
    select right([Caption],len([Caption])-(dbo.fnMin(nullif(charindex(' - ',[Caption]),0),nullif(charindex(' · ',[Caption]),0)))-3) as SubStr
    from <YourTableName>
) t

The inner select statement gets the all the values RIGHT of the first "-" or "·". Then the outer select will get everything LEFT of the second "-" or "·"


I've edited my code to account for no second "-" or "·"

EDIT: I can't use sqlfiddle since this solution requires a function... but here is what I was able run in my sandbox environment...

create table dbo.Interfaces (Caption varchar(1000))
go
insert into dbo.Interfaces
values
('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'),
('VPN 3030  - PCI Fast Ethernet')
go

create function dbo.fnMin( @P1 int, @P2 int)
    returns int
as
begin
    return case when @P1 > @P2 then coalesce(@P2,@P1) else coalesce(@P1,@P2) end;
end
go

select coalesce(left(SubStr, dbo.fnMin(nullif(charindex(' - ',SubStr),0),nullif(charindex(' · ',SubStr),0)) ),SubStr)
from (
    select right([Caption],len([Caption])-(dbo.fnMin(nullif(charindex(' - ',[Caption]),0),nullif(charindex(' · ',[Caption]),0)))-3) as SubStr
    from dbo.Interfaces
) t
go

and here is the result...

igabitEthernet1/0/1 
astEthernet1/0/43 
igabitEthernet1/0/24 
igabitEthernet1/0/25 
CI Fast Ethernet

(5 row(s) affected)

If you can't figure it out from this code... it's time to hit the books mister. ;)