Update/Delete part of a field - SQL Server Forums

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

Transact-SQL (2000)

Update/Delete part of a field

Author

Topic

cblatchford
Starting Member

40 Posts

Posted - 2006-06-14 : 04:18:26

Hi,

How would I go about updating a phone number, say 020 723 4567 to 723 4567.

Basically I want to remove the 020 part of the number, is this an update query or a partial delete query or something? Any links would be appreciated..

Thanks

CB

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-14 : 04:43:55

quote:
Originally posted by cblatchford

Hi,

How would I go about updating a phone number, say 020 723 4567 to 723 4567.

Basically I want to remove the 020 part of the number, is this an update query or a partial delete query or something? Any links would be appreciated..

Thanks

CB

UPDATE MyTable SET MyField = SUBSTRING(MyField, 5, 7995) WHERE MyField LIKE '020 %'

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-14 : 04:49:46

If it is for display, then instead of updation, do

Select SUBSTRING(column, 5, len(column)) from yourTable

Madhivanan

Failing to plan is Planning to fail

cblatchford
Starting Member

40 Posts

Posted - 2006-06-14 : 04:52:23

Hi,

Its not for display I need to actually modify the data; I tried your suggestion Peso but it throws up an error of..

Expression Result Length Exceeds the Maximum. 8000 Max, 15988 Found.

Any ideas!?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-14 : 04:54:39

quote:
Originally posted by cblatchford

Hi,

Its not for display I need to actually modify the data; I tried your suggestion Peso but it throws up an error of..

Expression Result Length Exceeds the Maximum. 8000 Max, 15988 Found.

Any ideas!?

Decrease 7995 to 7994 or any number smaller that holds complete field. If field is 50 varchar, use 50.
Or actually


UPDATE MyTable SET MyField = SUBSTRING(MyField, 5, LEN(MyField) - 4) WHERE MyField LIKE '020 %'

Peter Larsson
Helsingborg, Sweden

cblatchford
Starting Member

40 Posts

Posted - 2006-06-14 : 05:03:36

Thanks Peso, that last query worked a treat. Thanks for your help on this sunny(not) uk morning..

CB

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources