conditional update within value

18 Mar 2006 6:28 PM

JR

I have a column of data in SQL Server 2000 that I need to replace
values within it with new values. I know how to use CASE statements to
do conditional updates but not how to do this. Here is an example, not
the real example as the values relevant to my company would mean little
to anyone.

If value contains "name", replace it with "fullname"
If value contains "address", replace it with "fulladdress"
and so on...

What I want to do in the field is the following:

Field value now: abc##name##123
Field after change: abc##fullname###123

Field value now: asdlfkjlsdkafjnameasldfjk123
Field after change: asdlfkjlsdkafjfullnameasldfjk123

Field value now: adlsfkjaddresslksdfj34
Field after change: adlsfkjfulladdresslksdfj34

And update all rows in the approriate column with the above logic.

Any ideas?

Thanks.

JR

18 Mar 2006 7:10 PM

Tom Cooper

You don't need a Case statement to do this, you can use

Update #t Set foo = Replace (Replace (foo, 'address', 'fulladdress'),
'name', 'fullname')
Where foo Like '%name%' Or foo Like '%address%'

You could also do it with a Case statement like

Update #t Set foo = Case
When foo Like '%name%' Then Replace (foo, 'name', 'fullname')
When foo Like '%address%' Then Replace (foo, 'address', 'fulladdress')
Else foo
End
Where foo Like '%name%' Or foo Like '%address%'

Please note, however, that depending on your data, those two statements may
do different things. If a row has both "name" and "address" in that column,
the first update statement will change both name and address, but the Case
statement version will update only name to fullname, but won't change
address in that row.

Tom

Show quote

"JR" <jrik***@yahoo.com> wrote in message
news:1142706489.831624.92670@j33g2000cwa.googlegroups.com...
>I have a column of data in SQL Server 2000 that I need to replace
> values within it with new values. I know how to use CASE statements to
> do conditional updates but not how to do this. Here is an example, not
> the real example as the values relevant to my company would mean little
> to anyone.
>
> If value contains "name", replace it with "fullname"
> If value contains "address", replace it with "fulladdress"
> and so on...
>
> What I want to do in the field is the following:
>
> Field value now: abc##name##123
> Field after change: abc##fullname###123
>
> Field value now: asdlfkjlsdkafjnameasldfjk123
> Field after change: asdlfkjlsdkafjfullnameasldfjk123
>
> Field value now: adlsfkjaddresslksdfj34
> Field after change: adlsfkjfulladdresslksdfj34
>
> And update all rows in the approriate column with the above logic.
>
> Any ideas?
>
> Thanks.
>
> JR
>

19 Mar 2006 9:52 PM

Damien

You might want to have a look at STUFF as well, although REPLACE may well do
the trick.

The thing about CASE expressions is that they are 'falling rock' ie for the
first WHEN condition it finds to be true, it will return the THEN bit and
exit the statement. So if your string has multiple bits that need to
replacing, you'll need to run the UPDATE multiple times.

Hope that helps.

Damien

Show quote

"JR" wrote:

> I have a column of data in SQL Server 2000 that I need to replace
> values within it with new values. I know how to use CASE statements to
> do conditional updates but not how to do this. Here is an example, not
> the real example as the values relevant to my company would mean little
> to anyone.
>
> If value contains "name", replace it with "fullname"
> If value contains "address", replace it with "fulladdress"
> and so on...
>
> What I want to do in the field is the following:
>
> Field value now: abc##name##123
> Field after change: abc##fullname###123
>
> Field value now: asdlfkjlsdkafjnameasldfjk123
> Field after change: asdlfkjlsdkafjfullnameasldfjk123
>
> Field value now: adlsfkjaddresslksdfj34
> Field after change: adlsfkjfulladdresslksdfj34
>
> And update all rows in the approriate column with the above logic.
>
> Any ideas?
>
> Thanks.
>
> JR
>
>