SQL Server : how to split a string using a comma as a separator [duplicate]

Question

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?

see stackoverflow.com/questions/10914576/tsql-split-string nod Kevchadders — Ingo
– Ingo, Commented Feb 13, 2013 at 11:45
Welcome to SO. Please try searching before posting, this question has been asked many times before. — Bridge
– Bridge, Commented Feb 13, 2013 at 11:45
Thank you, I did search extensively, but found nothing to match my exact criteria. — Jediwannabe
– Jediwannabe, Commented Feb 14, 2013 at 9:41

TechDo · Accepted Answer · 2013-02-13 11:47:17Z

18

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

answered Feb 13, 2013 at 11:47

TechDo

18.7k3 gold badges55 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jediwannabe Over a year ago

Thank you! I was able to adapt this to do exactly what I needed.

Jediwannabe Over a year ago

Code for reference - 'UPDATE Asset SET Latitude = (SELECT left(UDFChar1, charindex(',', UDFChar1)-1)), Longitude = (SELECT right(UDFChar1, len(UDFChar1)-charindex(',', UDFChar1)))'

Collectives™ on Stack Overflow

SQL Server : how to split a string using a comma as a separator [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related