SEO

#1 (**permalink**) 02-28-2008, 06:49 PM

I have a column in one of my tables that needs to have about half of its
values updated.

The data looks like this:

A\B0andSomeVaryingStuff.mp3

I'd like to update it to:

A\C-B0andSomeVaryingStuff.mp3

The A, \-sign, B, C, .mp3 and - are literals. The
"andSomeVaryingStuff" changes. All I want to do is to replace the \B
with \C-B.

So I need to say "Find the instances where there is an A\B0 and replace
the A\B0 with A\C-B0 leaving the andSomeVaryingStuff.mp3 the same."

Note that every row will not need to be updated -- only the ones where
the column data begins A\B0.

Can you help?

Thank you,

-- Rick

#2 (**permalink**) 02-28-2008, 06:49 PM

You could use the REPLACE function.

As a safeguard make a new column on your table and run this statement....

UPDATE mpTable SET myNewColumn = REPLACE(myOriginalColumn, 'A\BO', 'A\C-B0')

If it works good, you may be able to then use "SET myOriginalColumn" and
remove the new column. May need tweaked, but see what happens?

--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz

"Guinness Mann" <GMann@dublin.com> wrote in message
news:MPG.1a22a815a633b89898972a@news.newsguy.com.. .
> I have a column in one of my tables that needs to have about half of its
> values updated.
>
> The data looks like this:
>
> A\B0andSomeVaryingStuff.mp3
>
> I'd like to update it to:
>
> A\C-B0andSomeVaryingStuff.mp3
>
> The A, \-sign, B, C, .mp3 and - are literals. The
> "andSomeVaryingStuff" changes. All I want to do is to replace the \B
> with \C-B.
>
> So I need to say "Find the instances where there is an A\B0 and replace
> the A\B0 with A\C-B0 leaving the andSomeVaryingStuff.mp3 the same."
>
> Note that every row will not need to be updated -- only the ones where
> the column data begins A\B0.
>
> Can you help?
>
> Thank you,
>
> -- Rick

#3 (**permalink**) 02-28-2008, 06:49 PM

In article <ug8ub.4379$zz.1972696130@newssvr30.news.prodigy.c om>,
jerry@antech.biz.nospam says...
> You could use the REPLACE function.
>
> UPDATE mpTable SET myNewColumn = REPLACE(myOriginalColumn, 'A\BO', 'A\C-B0')

I like it. I'm a little confused, though. Don't I need a WHERE clause?

-- Rick

#4 (**permalink**) 02-28-2008, 06:50 PM

No where clause is required unless you want to selectively update certain
data based on column values.

You could do this if you want...

UPDATE mpTable SET myNewColumn = REPLACE(myOriginalColumn, 'A\BO', 'A\C-B0')
Where myOriginalColumn LIKE '%A\BO%'

But if you are making a new column for the modified (replaced) value it will
not show up since the where clause restricts it to only the rows that need
updated. If you are not making a new column, then the where clause would be
great. Another thing... if you want this to run periodically you can setup
a Sql Agent Job to execute this statement as T-SQL and give it a schedule,
or even run when the server is at idle. Of course, the best practice would
be to eliminate the data entry problem when it's entered. But anyway....

--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz

"Jerry Boone" <jerry@antech.biz.nospam> wrote in message
news:ug8ub.4379$zz.1972696130@newssvr30.news.prodi gy.com...
> You could use the REPLACE function.
>
> As a safeguard make a new column on your table and run this statement....
>
> UPDATE mpTable SET myNewColumn = REPLACE(myOriginalColumn, 'A\BO',
'A\C-B0')
>
> If it works good, you may be able to then use "SET myOriginalColumn" and
> remove the new column. May need tweaked, but see what happens?
>
>

>
> --
> Jerry Boone
> Analytical Technologies, Inc.
> http://www.antech.biz
>
>
> "Guinness Mann" <GMann@dublin.com> wrote in message
> news:MPG.1a22a815a633b89898972a@news.newsguy.com.. .
> > I have a column in one of my tables that needs to have about half of its
> > values updated.
> >
> > The data looks like this:
> >
> > A\B0andSomeVaryingStuff.mp3
> >
> > I'd like to update it to:
> >
> > A\C-B0andSomeVaryingStuff.mp3
> >
> > The A, \-sign, B, C, .mp3 and - are literals. The
> > "andSomeVaryingStuff" changes. All I want to do is to replace the \B
> > with \C-B.
> >
> > So I need to say "Find the instances where there is an A\B0 and replace
> > the A\B0 with A\C-B0 leaving the andSomeVaryingStuff.mp3 the same."
> >
> > Note that every row will not need to be updated -- only the ones where
> > the column data begins A\B0.
> >
> > Can you help?
> >
> > Thank you,
> >
> > -- Rick
>
>

#5 (**permalink**) 02-28-2008, 06:50 PM

In article <Htaub.826$xE6.230763650@newssvr11.news.prodigy.co m>,
jerry@antech.biz.nospam says...
> You could do this if you want...
>
> UPDATE mpTable SET myNewColumn = REPLACE(myOriginalColumn,
> 'A\BO', 'A\C-B0') Where myOriginalColumn LIKE '%A\BO%'

Thanks, Jerry. It worked great as originally posted. After I posted
the query about WHERE (yuk, yuk) I tried it on a restricted range and it
worked so I updated the data in place. (I have a backup.)

> ...the best practice would be to eliminate the data entry
> problem when it's entered.

Roger that. I found the data entry problem halfway through the entry
process and fixed it then. This is a one-time update to fix the entries
done before I fixed the process.

Thanks again!

-- Rick

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Help with UPDATE