CREATE TABLE foo(Places varchar(50));
INSERT foo(Places) VALUES
('MARKET @123 NORTH RD'),
('HARDWARE @600 Main RD ;W1'),
('MAIN AV / NORTH RD'),
('500 NORTH RD @LIBRARY'),
('500 ANYSTREET ; *** SIDEWALK****'),
('MARKET @123 NORTH RD'),
('700 ANYSTREET'),
('(088.12345,088.12345) ;75 SOUTH RD');

Select
[Address] = CASE WHEN PATINDEX('%@[0-9]%' , Places) = 0 THEN     (SUBSTRING(Places, 0, CHARINDEX('@',Places)))
                WHEN PATINDEX('%@[a-z]%' , Places) = 0 THEN     (SUBSTRING(Places, CHARINDEX('@',Places) + 1, LEN(Places)))
                WHEN PATINDEX('%;[0-9]%' , Places) = 0 THEN (SUBSTRING(Places, CHARINDEX(';',Places) + 1, LEN(Places)))
                WHEN PATINDEX('[0-9]%' , Places) = 1 THEN (SUBSTRING(Places, 0, CHARINDEX(';',Places)))
                WHEN PATINDEX('%/%' , Places) > 0 THEN Places END

From dbo.foo