Update A Portion Of A Field Text Value

[mabis]

Running on SQL 2000. My field I am trying to update is from table tblToner with field name description that is a varchar field with max 8000 char. Nulls are allowed.

I want to avoid changing hundreds of fields for the same reason. For example this field value...

HP Series I/LJ/LJ+ /LJ HP 500+

is very common. I would like to update it and all others that contain 'HP' and 'LJ' to read 'Hewlett Packard' and 'LaserJet'

In the end it should be Hewlett Packard Series I/LaserJet/LaserJet+/LaserJet HewlettPackard 500+

Kindly offer a solution. Your help is greatly appreciated.

 

[gmmastros]

You will want to use the replace function to do this. You'll need to be careful because you could inadvertently update the wrong data. Let me explain.

Suppose your data was...

AT
CAT
RAT

And then you replace AT with 'Blah'. You would end up getting...

Blah
CBlah
RBlah

The basic command would be something like this...
[tt][blue]
Update tblToner
Set FieldName = Replace(FieldName, 'HP', 'Hewlett Packard')

Update tblToner
Set FieldName = Replace(FieldName, 'LJ', 'Laser Jet')
[/blue][/tt]
Because of the potential to 'mess up' your data, I encourage you to make a backup of your database before doing this.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Before running this command, you can 'see' the results by selecting it first.

Select Distinct
       Replace(FieldName, 'HP', 'Hewlett Packard')
From   tblToner
Where  FieldName Like '%HP%'

If the data appears ok, then you should be fine to run the update command.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom