Replace partial data on a Colum!!

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

General SQL Server Forums

New to SQL Server Programming

Replace partial data on a Colum!!

Author

Topic

miguelcesario
Starting Member

2 Posts

Posted - 2006-08-29 : 07:04:27

Hello,

I have a table in witch i would like to replace all the data form one columm following a criteria.

The criteria is:

Where 1010222222 will be 0020222222

So i want to make a script that enables me to substitute all the data in that columm that begin by "101" for "002"

Any help?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-29 : 07:09:42

UPDATE MyTable SET MyField = '002' + SUBSTRING(MyField, 4, 8000)
WHERE MyField LIKE '101%'

Peter Larsson
Helsingborg, Sweden

miguelcesario
Starting Member

2 Posts

Posted - 2006-08-29 : 08:50:09

Thx so much. It worked like a charm. Just had to modify the 8000 value to 4000, cause i got and "exceded error legth". What does that parameter (8000) really stands for?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-29 : 09:40:43

The maximum number of characters to fetch from the field or variable.

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-29 : 10:01:23

If it is only ofr display then

Select '002' + SUBSTRING(MyField, 4, 8000) from table
WHERE MyField LIKE '101%'

Madhivanan

Failing to plan is Planning to fail

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-29 : 10:18:55

Isn't the Stuff function more easier ?

SELECT STUFF('1010222222', 1, 3, '002')

Select STUFF(MyField, 1, 3, '002')
from table
WHERE MyField LIKE '101%'

Srinika

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-29 : 10:27:34

Try it on a million row table. STUFF first deletes the characters you specify, and then concatenate the string or strings (if stuff in the middle).

With my suggestion there is only one operation, a concatenate.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources