replace varbinary - SQL Server Forums

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 varbinary

Author

Topic

efervescente
Starting Member

6 Posts

Posted - 2006-12-12 : 21:41:29

hello.
i want to do something with this database but i don't know how:

there is this column that is varbinary and i want to replace just one part of its content, for example:

this is its content:
'CC08FF806785'
and i just want to change the FF for AA.

i tried using query analyzer with this:

update table
set column=replace(column,'FF','AA')

but it says something like conversion isn't allowed from data varchar to varbinary and that i should use the function "convert" but i don't know how to use that!

does anyone know how to do that?
thanks!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-12-12 : 23:29:46

You can't replace hex digits with strings, you have to use hex for each parameter:

select cast(replace(0xCC08FF806785, 0xFF, 0xAA) as varbinary)

efervescente
Starting Member

6 Posts

Posted - 2006-12-12 : 23:52:28

oh. well...
i have many rows to replace, and hex content may vary on every row.
it's not "CC08FF806785" for all rows. do you get what i'm trying to say?
so i want to change FF for AA in all the rows of the table. is there a way to do this?
maybe something like this?

select cast(replace(table.column, 0xFF, 0xAA) as varbinary)

(this didn't work, it says "'table' isn't a name of a table or... used in the query.")

thanks in advance...!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-13 : 01:14:36

Bad move!
What about the hex value 0x0FF1 ?

Tell us why you think you need this update process.

Peter Larsson
Helsingborg, Sweden

efervescente
Starting Member

6 Posts

Posted - 2006-12-13 : 08:19:37

quote:
Originally posted by Peso

Bad move!
What about the hex value 0x0FF1 ?

Tell us why you think you need this update process.

Peter Larsson
Helsingborg, Sweden

i'm lost! 0x0FF1? update process?

sorry i didn't understand anything, i'm new to sql.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-13 : 08:59:20

Why do you want to update 0xFF to 0xAA ?

If you do a regular UPDATE, the hex value 0x0FF1 would be translated to 0x0AA1, and I don't think you want this.
You have be more specific about your requirements.

Can there be multiple FF's in a column?

Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-12-13 : 09:22:08

quote:
Originally posted by Peso

Why do you want to update 0xFF to 0xAA ?

If you do a regular UPDATE, the hex value 0x0FF1 would be translated to 0x0AA1, and I don't think you want this.
You have be more specific about your requirements.

Can there be multiple FF's in a column?

Peter Larsson
Helsingborg, Sweden

Are you sure about that? I get this:


select cast(replace(0x0FF1 , 0xFF, 0xAA) as varbinary) as MyHex
select cast(replace(0xFF10 , 0xFF, 0xAA) as varbinary) as MyHex

Results:

MyHex                                                          
-------------------------------------------------------------- 
0x0FF1

(1 row(s) affected)

MyHex                                                          
-------------------------------------------------------------- 
0xAA10

(1 row(s) affected)

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-13 : 09:24:29

I was mentioning the possiblity if he converted the varbinary column to a string first.
But you are right otherwise.

Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-12-13 : 09:27:55

This is a really odd situation -- can you explain why you need to do this? Please tell me you aren't combining multiple values all into 1 bit-masked binary column to "save space" ....

You also need to clearly define your specs .. do you want to convert your binary value to a hexidecimal string and replace all isntances of FF with AA ? (I doubt it) Or, do you want to set the 7th and 8th bytes to 'AA' in your varbinary column? (more likely). Or, something else? Your specs are not clearly stated.

quote:

this is its content:
'CC08FF806785'
and i just want to change the FF for AA.

Any way, the proper way to do that using binary arithmetic would be:

1) bitwise AND it with FFFF00FFFFFF
2) then bitwise OR it with 0000AA000000

So that means:

select convert(binary(6), convert(bigint, 0xCC08FF806785) & convert(bigint,0xFFFF00FFFFFF ) | convert(bigint,0x0000AA000000))

(In SQL Server, you cannot use bitwise operators on binary datatypes (!!) they must be converted to integer first) ....

- Jeff

efervescente
Starting Member

6 Posts

Posted - 2006-12-13 : 10:27:46

http://img300.imageshack.us/my.php?image=sqlvarbinaryax9.jpg

there's a pic of the column.
i want to change all 20001E000000000000FF to FFFFFFFFFFFFFFFFFFFF, and as you can see, it's randomly placed, and content in every row varies.

is it clear-er now? i hope it is!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-13 : 10:38:50

Use the technique displayed by Michael Valentine Jones above...

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-13 : 10:40:14

SELECT <YourColumnNameHere>, CAST(REPLACE(<YourColumnNameHere>, 0x20001E000000000000FF, 0xFFFFFFFFFFFFFFFFFFFF) AS VARBINARY) FROM <YourTableNameHere>

Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-12-13 : 11:40:11

quote:
Originally posted by Peso

I was mentioning the possiblity if he converted the varbinary column to a string first.
But you are right otherwise.

Peter Larsson
Helsingborg, Sweden

REPLACE casts the varbinary string to varchar first, so it can only do replaces at the character level. That's why is doesn't work when the FF crosses a character boundry.

CODO ERGO SUM

efervescente
Starting Member

6 Posts

Posted - 2006-12-13 : 14:09:19

Ok Peso, it works. i mean it is executed but it doesn't replace anything.

results show the column before changes were made on the left side, and on the right side the same column (called "column with no name") chaged.
i open the table to check if it was changed but nothing was changed. what's wrong?

excuse my noobie-ness.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-13 : 14:33:07

The query I posted above was only for visual inspection that the correct portion of the binary value is updated.

In order to actually update the table, you now need to run this query


UPDATE <YourTableNameHere>
SET    <YourColumnNameHere> = CAST(REPLACE(<YourColumnNameHere>, 0x20001E000000000000FF, 0xFFFFFFFFFFFFFFFFFFFF) AS VARBINARY)

Peter Larsson
Helsingborg, Sweden

efervescente
Starting Member

6 Posts

Posted - 2006-12-13 : 14:55:02

ohh yes... that UPDATE thing.
thank you very much sir. thanks everyone who helped.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources