move partial value from one column 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 2005 Forums

Transact-SQL (2005)

move partial value from one column to another

Author

Topic

darenkov
Yak Posting Veteran

90 Posts

Posted - 2007-11-30 : 21:57:38

i am trying to move the numbers from column one into the next column (postcode). this is in the same table called companydetails

here is my sample data:

SUB/STATE/PC POSTCODE
------------------|--------------
Windy Hills 6045 NULL
Chestermere 6189 NULL
Mt Lawley 7468 NULL

here is my lame attempt to do this:

update companydetails set [Postcode] = (select right([sub/state/pc],4) from companydetails)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-01 : 01:23:53

If you are sure that last 4 digits are numbers then

update companydetails set [Postcode]=right([sub/state/pc],4)

Madhivanan

Failing to plan is Planning to fail

ranganath
Posting Yak Master

209 Posts

Posted - 2007-12-01 : 01:33:35

hi,

try this

DECLARE @T TABLE ([SUB/STATE/PC] varchar(1000), postcode int)
INSERT INTO @T
SELECT 'Windy Hills 6045',NULL UNION ALL
SELECT 'Chestermere 6189', NULL UNION ALL
SELECT 'Mt Lawley 7468', NULL

--SELECT * FROM @T

UPDATE A
SET Postcode = stuff(RIGHT ([SUB/STATE/PC],charindex(' ',reverse([SUB/STATE/PC]))),1,1,'')
FROM @T A

SELECT * FROM @T

darenkov
Yak Posting Veteran

90 Posts

Posted - 2007-12-01 : 04:43:22

thanks guys

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources