Problem with REPLACE with binary strings

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

SQL Server 2000 Forums

SQL Server Development (2000)

Problem with REPLACE with binary strings

Author

Topic

jammerqwe
Starting Member

3 Posts

Posted - 2008-07-17 : 04:48:15

Hi,

I'm using binary to store hierarchical data.

I have a problem with the following line in my procedure inside an Update

binPath = Cast(REPLACE(binPath,
@oldParentPath,
@newParentPath) as VARBINARY )

I'm trying to replace
0x0000036400000368
with
0x0000036400000365
in
0x00000364000003680000039E

and getting

0x000000000364000003650000039E

ie with 4 extra 0s on the left.

Works fine apart from these extra zeros.

Does anyone have an idea please?

Many thanks

jammerqwe

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-07-17 : 05:39:33

This works, can't see the problem?!?

select replace(cast('0x00000364000003680000039E' as varbinary),'0x0000036400000368', '0x0000036400000365')

jammerqwe
Starting Member

3 Posts

Posted - 2008-07-21 : 03:05:46

Thanks very much for taking the time to help.

I've tried to incorporate your suggestion, but had to put in an extra convert to get it to parse, but still no go I'm afraid.

I've now tried:-

Set
binPath = CONVERT(VARBINARY(MAX),
Replace(Convert(VARBINARY(MAX),binPath),
@oldParentPath,
@newParentPath) )

WHERE binPath >= @curPath
AND binPath < @newPath

but still get the extra 4 0000

If you have any further ideas I would be very grateful.

jammerqwe

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-21 : 03:39:17

[code]DECLARE @Original VARBINARY(100),
@Search VARBINARY(100),
@Replace VARBINARY(100),
@Length INT

SELECT @Original = 0x00000364000003680000039E,
@Search = 0x0000036400000368,
@Replace = 0x0000036400000365

-- Peso
SET @Length = DATALENGTH(@Original)
SET @Original = CONVERT(VARBINARY(100), REPLACE(@Original, @Search, @Replace))

SET @Length = DATALENGTH(@Original) - @Length
SET @Original = SUBSTRING(@Original, @Length + 1, 100)

SELECT @Original[/code]

E 12°55'05.25"
N 56°04'39.16"

VGuyz
Posting Yak Master

121 Posts

Posted - 2008-07-21 : 06:34:09

Hi,
convert your parameter to varchar from binary.

select replace ('0x00000364000003680000039E',
'0x0000036400000368','0x0000036400000365')

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-21 : 06:37:19

That is strings (VARCHAR), not BINARY values.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts