Cutting values from one colum to another

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Cutting values from one colum to another

Author

Topic

Incognito
Starting Member

49 Posts

Posted - 2002-03-28 : 11:31:10

Hello,

I Have Two colums: postcode and place.
Now all the values are stored in the postcode column, place is NULL everywhere.
Example:
Postcode Place
7500 DP AMSTERDAM, NULLL
6700 EP ROTTERDAM, NULL

What I want is to put 'AMSTERDAM' and 'ROTTERDAM' in the Place column. Is there a quick way to do this. ( I have about 12000 records to update)

So:

Postcode Place
7500 DP ASTERDAM
6700 EP ROTTERDAM

Thnx all,

Incognito...

PS: The postcode column is everywhere the same. I mean first 4 characters_space_2 characters_and then the place name.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-28 : 11:41:00

UPDATE myTable SET Place=SubString(PostCode, 9, 99)

If you don't want the trailing comma included, use this:

UPDATE myTable SET Place=SubString(PostCode, 9, CharIndex(',', PostCode)-9)

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-03-28 : 19:26:11

And then to remove the Place from the Postcode column, AFTER you have successfully updated the Place column, do this:

UPDATE myTable SET PostCode = SubString(PostCode, 1, 7)

NOTE: Make sure you backup your database before doing any of these updates... just in case.

------------------------
GENERAL-ly speaking...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources