how to split data

I'd use a tally table method to do this if there are many rows to be processed, so long as the split occurs on every comma http://www.sqlservercentral.com/articles/T-SQL/62867/

To add to @Fatherjack's suggestion, here is an example that will at least split the example data into the intitial three major groups. You can go from there to get the split down to what you really want.

However, as this is a complicated split, it will probably be better done in SQLCLR using RegEx. I think @Matt Whitfield may swing by and suggest the same. It will be quicker and less hassle to do it that way (if you can code in C# or VB.Net).

The SplitString function comes from the article linked by @Fatherjack, the Nums function is a dynamic tally inline table function and works like the Tally table that is mentioned in the article.

You can see how clunky it is already in my example, and you would need to use SplitString again to achieve the final result.

IF EXISTS (SELECT * FROM sys.objects WHERE name ='StringSplit' AND type = 'IF')
    BEGIN
        DROP FUNCTION dbo.StringSplit
    END
GO
CREATE FUNCTION dbo.StringSplit
    (@String varchar(max),
     @Splitter varchar(10))
RETURNS TABLE
RETURN
    (SELECT SUBSTRING(@String, N + 1, CHARINDEX(@Splitter, @String, N + 1) - N - 1) data
     FROM   dbo.Nums(LEN(@String)) N2
     WHERE  N < LEN(@String)
            AND SUBSTRING(@String, N, 1) = @Splitter)
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name ='Nums' AND type = 'IF')
    BEGIN
        DROP FUNCTION dbo.Nums
    END
GO
CREATE FUNCTION dbo.Nums (@m AS bigint)
RETURNS TABLE
    AS
RETURN
    WITH    t0
              AS (SELECT    n = 1
                  UNION ALL
                  SELECT    n = 1),
            t1
              AS (SELECT    n = 1
                  FROM      t0 AS a,
                            t0 AS b),
            t2
              AS (SELECT    n = 1
                  FROM      t1 AS a,
                            t1 AS b),
            t3
              AS (SELECT    n = 1
                  FROM      t2 AS a,
                            t2 AS b),
            t4
              AS (SELECT    n = 1
                  FROM      t3 AS a,
                            t3 AS b),
            t5
              AS (SELECT    n = 1
                  FROM      t4 AS a,
                            t4 AS b),
            result
              AS (SELECT    ROW_NUMBER() OVER (ORDER BY n) AS n
                  FROM      t5)
    SELECT  n
    FROM    result
    WHERE   n <= @m
GO

DECLARE @Splitter char(1)
DECLARE @Parameter varchar(8000)

SELECT @Splitter = '_',
       @Parameter = '(17.685895196738677,75.926513671875;State Highway 151, Shoaaaa, Maaaaaa, Iaaaaa)_(17.675427818339383,77.5634765625;Naaaa Haaa 9, AaaaaPa, Iaaaaaa)_(18.375379094031824,74.564208984375;Saaaaa 151, Maaaaaa, Iaaaa)'
SELECT  @Parameter = @Splitter + REPLACE(REPLACE(@Parameter, ')', ''), '(', '') + @Splitter ;

SELECT  data
FROM    dbo.StringSplit(@Parameter, @Splitter) SS

There are several very fine answers here. But I am going to particularly agree with Matt Whitfield that regex is probably the way to go. In addition to the fine article he linked to, I have found http://www.simple-talk.com/sql/t-sql-programming/clr-assembly-regex-functions-for-sql-server-by-example/ to be very helpful in the past.

Also, depending on how you ultimately plan to use this data it may make sense to do it in the application layer instead of trying to do it within the database. While you certainly can, as many posters here expertly point out, string manipulation is not a strength of SQL.

If you can use the CLR, do - as @WilliamD says. You haven't said anything about the data volume, whether this is a one-time operation etc etc.

You can find an example of a regular expression matcher in my CLR Presentation from SQL Bits.

The regular expression to parse that is:

\(([0-9]+\.[0-9]+),([0-9]+\.[0-9]+);([^)]+)\)