SQL Parsing/Update/Insert Query

[LFDavidson]

New to this site and new to learning SQL so hopefully this is an acceptable post:

I have two tables in MSSQL, one that contains a field with concatenated data that I need to query and put the data into two separate fields in another table.

Table - Contact

I identify the correct records when field "First" has the code GPS. The content of the field "Contact_Point" holds the concatenated GPS Coordinates similar to 35.8516720N,78.5809430W and the number of decimal places will vary.

Table - Customer

I need to separate the GPS Coordinates in the Table Contact/Field Contact_Point and insert them into the correct records in the fields "X-Coordinate" and "Y-Coordinate" in the Customer Table.

"ID" Field in Customer = "Customer_ID" Field in Contact

Looking for help on how to write the query and update to do this.

 

[gmmastros]

The string parsing code should be working now (the syntax for splitting the 1 column in to 2).

Why don't you attempt to write the update query. If you want, you can post it here and I will look at it before you actually run it. I should be available for the next 1 1/2 hours.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[LFDavidson]

select * FROM Contact
		Where  [first] = 'GPS'
       		And CharIndex(',', Contact_Point) > 0
Update Customer
	Set	y_coordinate = Left(Contact_Point, CharIndex(',', Contact_Point) - 1),
		x_coordinate = LTrim(Right(Contact_Point, Len(Contact_Point)) - CharIndex(',', Contact_Point))
From Customer
	INNER JOIN Contact ON Customer.Id = Contact.Customer_ID

This code returns the following:

(5276 row(s) affected)

Server: Msg 245, Level 16, State 1, Line 4
Syntax error converting the varchar value '35.811020999N,80.874398999W' to a column of data type int.

 

[gmmastros]

Close. Good try. I notice that the parenthesis is still in the wrong place. My fault because I mentioned it in a previous post but didn't actually fix it. Other than the parenthesis, the only addition to the query I have is to add the where clause. Here:

Update Customer
Set    y_coordinate = Left(Contact_Point, CharIndex(',', Contact_Point) - 1),
       x_coordinate = LTrim(Right(Contact_Point, Len(Contact_Point) - CharIndex(',', Contact_Point)))
From   Customer
       INNER JOIN Contact ON Customer.Id = Contact.Customer_ID
Where  [first] = 'GPS'
       And CharIndex(',', Contact_Point) > 0

Without the where clause on the CharIndex(...) > 0, if there are any records where the contact_point does not contain an comma anywhere, you would get an error. With the addition to the where clause, if the customer record does not have a comma, the corresponding record in the contact table will not get updated (as it shouldn't).

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[LFDavidson]

Thanks for all the help... That looks like its got it..