CODEWITHSUNDEEP
sql-serversql-server-2005
JonnyPoet
JonnyPoet

Reputation: 3

Convert string to integer and update into other field

I have a table that has the following structure

[zip] = <zip, nvarchar(4),>
[cityZipID] = <cityZipID, int,>

In the zip column there is a string containing 4 digits and this is a number between 1000 an 1239 gut stored as a string.

For some reason I need to calculate an other value out of this so I need to convert the string into an integer and store it into an other column called cityZipID. I want to do this using SQL Server Management Studio because it has to convert about 32000 lines so I cannot easily do it by hand.

I tried the following but get only an error message when trying to execute it

UPDATE [MyTestData].[dbo].[Addresses]
   SET [cityZipID] = ((int)[zip])/10 -100
 WHERE [city] = 'Wien'

The column of cityZipID is null in the moment and should be filled with numbers for the districts like the plzl for the first district is 1010 the 12th district is 1120 So the calculation would result in 1120 / 10 = 112 -100 = 12 and this would be the wanted result.

Any help would be appreciated.

Jonny

Upvotes: 0

Views: 2758

Answers (1)

selva kumar
selva kumar

Reputation: 1156

Try this query

UPDATE [MyTestData].[dbo].[Addresses]
   SET [cityZipID] = (Convert(Int,[zip])/10) -100
 WHERE [city] = 'Wien'

Upvotes: 1

Related Questions