Need help with a replacing query

[G_Ozama]

Hi I have a DB running on MSSQL 2000

I need to execute a query to do the following:

Inside the table named User there's a column named UserList and a column named UserType

UserList is varbinary type, 180 lenght
UserType is tinyint type, 1 lenght

The "list" is setted by 6 numbers each item, ex.:

0x0900001A0000280000FF0000126800286800FF0000FF0000

Where "FF0000" is a "null" value.

I need to replace all "280000" with "FF0000" values, where UserType is 17, knowing that the "280000" can be at any part of the "list".

Is there a query to do it easily?


Thx in advance, and sorry for my english...

[MAK]

--Step 1 create this procedure
use [My Database Test]
go

CREATE function dbo.sp_hexadecimal
(@binvalue varbinary(256))
returns varchar(500)
AS
BEGIN
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
return @charvalue
END

--Step 2

use [My Database Test]
go
select userList, replace(dbo.sp_hexadecimal(UserList),'280000','FF0 000') as Afterreplace from dbo.mytable3 where Usertype=17

[G_Ozama]

hi MAK ur answer was very useful, but i still can't get what i need to do... when i create an trigger to auto-change the 280000 into FF0000 when its updated all my values got messed

for exemple i set the UserList = 0x110000280000FF0000
and i get this value as the UserList = 0x3078313130303030464630303030464630303030

30 = 0
78 = x
31 = 1
46 = F

it's kinda working ok, it changed the 280000 into FF0000, but the values are not ok, i think it's some mistake in the dbo.sp_hexadecimal

i used this trigger:

CREATE TRIGGER 28_remove
ON Users
AFTER UPDATE
AS
DECLARE @User VARCHAR(10)
DECLARE @NewList VARCHAR(180)
DECLARE @Replace VARBINARY(180)
IF NOT UPDATE(MagicList)
BEGIN
RETURN
END

SELECT @User = (SELECT Name FROM Inserted)
SELECT @NewList = (SELECT REPLACE(dbo.sp_hexadecimal(UserList),'280000','FF0 000') FROM Inserted)
SELECT @Replace = (SELECT CONVERT(varbinary(180),@NewList))

UPDATE Users SET UserList = @Replace WHERE Name = @User

and i use this sample query to test:

UPDATE Users
SET UserList = 0x110000280000FF0000
WHERE Name = 'MyName'

PS.: the space on 'FF0000' appear by itself here =/