Extract street address from string with multiple delimiters and positions
Try this.

;With cte as
(
Select 
    places,
    StartHere = 
        CASE 
            WHEN PATINDEX('%@[0-9]%', places) &gt; 0 THEN PATINDEX('%@[0-9]%', places) + 1     -- @ plus number, start here
            WHEN PATINDEX('%;[0-9]%', places) &gt; 0 THEN PATINDEX('%;[0-9]%', places) + 1     -- ; plus number, start here
            ELSE 0
        END
    ,EndHere = 
        CASE
            WHEN PATINDEX('%@[a-z]%', places) &gt; 0 THEN PATINDEX('%@[a-z]%', places)   -- @plus letter, stop here        
            WHEN PATINDEX('%@[0-9]%', places) &gt; 0 AND PATINDEX('%;%', places) &gt; 0 THEN  PATINDEX('%;%', places)  --@ plus number stop on semicolon if there
            WHEN PATINDEX('%;[0-9]%', places) = 0 AND PATINDEX('%;%', places) &gt; 0 THEN PATINDEX('%;%', places)   --no semi plus number, so stop on semicolon if there
            ELSE len(places) + 1
        END
FROM
    foo
)
    SELECT 
        places, 
        StartHere
        ,EndHere
        ,SUBSTRING(places, StartHere, EndHere - StartHere)
    FROM 
        cte