SQL Server : how to split a string using a comma as a separator
36,895
Please try:
left(Col, charindex(',', Col)-1)
and
right(Col, len(Col)-charindex(',', Col))
sample
SELECT
LEFT(COL, CHARINDEX(',', Col)-1) Lattitude,
RIGHT(COL, LEN(COL)-CHARINDEX(',', Col)) Longitude
FROM(
SELECT '41.243223,-8.183913' Col
)x
Author by
Jediwannabe
Updated on August 16, 2020Comments
-
Jediwannabe almost 4 years
I need to populate columns in my database for Latitude and Longitude, however the original information is stored as a single string
eg.
UDFChar1 = 41.243223,-8.183913
I am guessing that the TRIM command will come in useful here, but I do not know how I can tell it to stop exactly on the comma for each half.
What I'm hoping to be able to come up with is a simple UPDATE query as per the below:
UPDATE Asset SET Lattitude = (SELECT LTRIM(UDFChar1)), Longitude = (SELECT RTRIM(UDFChar1))
but obviously with some extra work in the LTRIM and RTRIM parts so that I am only selecting the data up to, and not including the comma in UDFChar1
Any ideas on how to achieve this?