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 · P

Solution 1:

Try this

Declare@LeftMarkervarchar(3)
Declare@RightMarkervarchar(3)
    Set@LeftMarker=' - '--<--- Replace this with your choiceSet@RightMarker=' · '--<--- Replace this with your choiceDeclare@LMarkerLenintSet@LMarkerLen= LEN(@LeftMarker)

SELECTCaseWhen CHARINDEX(@RightMarker, Caption) >0ThenSUBSTRING(Caption, CHARINDEX(@LeftMarker, Caption) +@LMarkerLen, CHARINDEX(@RightMarker, Caption) - (CHARINDEX(@LeftMarker, Caption) +@LMarkerLen))
        ElseSUBSTRING(Caption, CHARINDEX(@LeftMarker, Caption) +@LMarkerLen, CHARINDEX(@LeftMarker, Caption, CHARINDEX(@LeftMarker, Caption) +@LMarkerLen) - (CHARINDEX(@LeftMarker, Caption) +@LMarkerLen))
        EndASResultFROM .......

Solution 2:

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

SELECTSUBSTRING(Caption, 
            CHARINDEX(' - ', Caption) +3, 
            CHARINDEX(' · ', Caption) - CHARINDEX('- ', Caption) + LEN(' · ') -3) 
FROM
  Foo
WHERE
  CHARINDEX(' · ', Caption) >0UNIONSELECTSUBSTRING(
  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.

Solution 3:

I created a min function for this.

createfunctiondbo.fnMin( @P1 int, @P2 int)
    returnsintasbeginreturncasewhen@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!

selectcoalesce(left(SubStr, dbo.fnMin(nullif(charindex(' - ',SubStr),0),nullif(charindex(' · ',SubStr),0)) ),SubStr)
from (
    selectright([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...

createtabledbo.Interfaces (Caption varchar(1000))
goinsertintodbo.Interfacesvalues
('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')
gocreatefunctiondbo.fnMin( @P1 int, @P2 int)
    returnsintasbeginreturncasewhen@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. ;)