Share
Explore BrainMass

Update statement

Here is the issue, a table namedPoints varchar(81) has values stored in a column named Point. There are two columns in the Points table, X and Y. Here is the code that created the table:
REATE TABLE dbo.POINTS (
I INT IDENTITY NOT NULL
,Point varchar(81) NOT NULL
,X decimal(38,10) NULL
,Y decimal(38,10) NULL
)
GO
INSERT dbo.POINTS (Point) VALUES ('(45.123,22.5)')
INSERT dbo.POINTS (Point) VALUES ('(7.95,3.45)')
INSERT dbo.POINTS (Point) VALUES ('(100.2,15.498)')
GO

I need a query using an UPDATE statement to populate X and Y from Point. THe data on the left side of the comma is X and on the right side of the comma is Y. I am guessing that either Substring or Charindex would beused but I am not sure.

Solution Preview

UPDATE points
SET
x = CAST(SUBSTRING(point,2,CHARINDEX(',',point)-2) AS ...

Solution Summary

An update statement is created to populate two columns with decimal values based on POINT table.

$2.19