How to split a comma separated value to columns together other columns

Question

I have a table like this

AccountID      GEO

CT-2000       9.9582925,-84.19607

I want to separate the comma delimited string into two columns

AccountID      LAT         LONG 

CT-2000       9.9582925    -84.19607

Answer 1

You may try with the next approach, using LEFT(), RIGHT(), LEN() and CHARINDEX() functions:

Table:

CREATE TABLE Data (
   AccountID varchar(7),      
   GEO varchar(50)
)
INSERT INTO Data
   (AccountID, GEO)
VALUES
   ('CT-2000', '9.9582925,-84.19607')

Statement:

SELECT 
   AccountID,
   LEFT(GEO, CHARINDEX(',', GEO) - 1) AS Lat,
   RIGHT(GEO, LEN(GEO) - CHARINDEX(',', GEO)) AS Long
FROM Data   

Result:

AccountID   Lat         Long
CT-2000     9.9582925   -84.19607