July 15, 2008 at 5:25 pm
I HAVE THREE TABLES. ONE TABLE STORES AN NVARCHAR VALUE nr:68EFC33E9E1B334ABF5F52971808D4BC
AND THE OTHER TABLE CONTAINS A BINARY VALUE 0x000FD4EB2F811A43A3D674D5FA7B5E95.
I WANT TO MATCH TRIM THE 0x OFF AND THE nr: AND RETURN RESULTS THAT MATCH.
MY PROBLEM IS I CAN CONVERT THE NVARCHAR FIELD AND REMOVE THE nr:
REPLACE(CAST((a.l_persid) AS NVARCHAR(100)),'nr:','')
BUT WHEN i TRY IT FOR THE BINARY VALUE TO CONVERT IT TO NVARCHAR IT RETURNS GARGABE
I.E
IF OBJECT_ID('tempdb..#res') IS NOT NULL
DROP TABLE #res
GO
CREATE TABLE #res
(
own_resource_uuidBINARY (16) NOT NULL
,resource_nameNVARCHAR(100) NOT NULL
)
GO
INSERT #res VALUES(0x000FD4EB2F811A43A3D674D5FA7B5E95 ,'ABTM' )
INSERT #res VALUES(0x00397CC0DF0DAB4CB5249BDD32B65AC6,'LIND' )
INSERT #res VALUES(0x004789917A5E5A49A94386DFBFFA83DD,'NMOB0SS' )
INSERT #res VALUES(0x00638BF12B3D2943AC9423E96EB7EDB2,'ESTER' )
INSERT #res VALUES(0x00731E7B27507A45892DEEE011260B06,'RHYB003' )
INSERT #res VALUES(0x0092FBF171E07649A3C7950691F581E7,'EYER' )
INSERT #res VALUES(0x009F12ACCEC4B44FACF749FD184F0FF9,'GREG009' )
INSERT #res VALUES(0x00A72914CCA78D4388BC8274F34D7F53,'HOMOB005' )
INSERT #res VALUES(0x00A8C06F63092E449EE7D98A75FFB669,'Ed-JFRENCH' )
INSERT #res VALUES(0x00E53AC14E9556448F821F43A95DF4B8,'LLCML002' )
INSERT #res VALUES(0x00F283F95D1A634EA2DA55190BDE3A4A,'EM-AVEN' )
INSERT #res VALUES(0x00F583D606524748A894AF08395135C3,'SKHMLO' )
INSERT #res VALUES(0x00F5A3DE265C9B42B5D273719ABB537E,'BNREG002' )
INSERT #res VALUES(0x01067B99119B9843B19E8AF2CFC3BED0,'BUENG' )
INSERT #res VALUES(0x014A29586A2FB445A6E0E281C58C1193,'C6084D11' )
INSERT #res VALUES(0x014ADE23762D5144A8228F69A71511BD,'LFREG016' )
INSERT #res VALUES(0x0159695AC83D0E40A1A9D237CD483BFF,'SBSA01' )
INSERT #res VALUES(0x0164A9D17742574F8399E858EA7454C3,'COLENE' )
SELECT CONVERT(VARCHAR(100),own_resource_uuid) FROM #res
BRINGS BACK GARBAGE
{™›˜C±žŠòÏþÐ
J)Xj/´E¦àâÅŒ“
JÞ#v-QD¨"i§½
YiZÈ=@¡©Ò7ÍH;ÿ
d©ÑwBWOƒ™èXêtTÃ
{™›˜C±žŠòÏþÐ
SELECT REPLACE(sys.fn_sqlvarbasetostr(own_resource_uuid),'0x',''),own_resource_uuid FROM ca_owned_resource
select replace(cast(( own_resource_uuid) as nvarchar(100)),'0x','') from #RES
--- RAN THIS AND IT RUNS VERY LONG AND I HAVE TO CANCEL IT
SELECT
a.l_persid
,REPLACE(CAST((a.l_persid) AS NVARCHAR(100)),'nr:','')
,b.resource_name
,b.own_resource_uuid
,REPLACE(sys.fn_sqlvarbasetostr(b.own_resource_uuid),'0x','')
from
lrel a left join ca_owned_resource b ON
REPLACE(CAST((a.l_persid) AS NVARCHAR(100)),'nr:','') =
REPLACE(sys.fn_sqlvarbasetostr(b.own_resource_uuid),'0x','')
Where
a.l_attr = 'asset' And a.r_attr = 'chgnr'
CAN I FIX THIS.. I KEEP GETTING JUNK BACK
July 15, 2008 at 11:00 pm
Ok here it goes...
Declare @binvalue varbinary(255)
Set @binvalue = 0x000FD4EB2F811A43A3D674D5FA7B5E95
declare @charvalue varchar(255)
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
Select @charvalue
I hope it will help you...
I got this code from
http://support.microsoft.com/kb/104829">
http://support.microsoft.com/kb/104829
check the link for details...
Atif Sheikh
July 16, 2008 at 1:40 am
I read the code and tested it and it works only if you hardcode the binary value you want to convert. But I want to use it on two tables....
Table A is fine I get the right value back.
select replace(cast((r_persid) as nvarchar(100)),'nr:','') from A where l_attr = 'asset' and r_attr = 'chg'
I want to join the two tables using r_persid field after removing 'nr:' at the beginning
and from table B
Declare @binvalue varbinary(255)
Set @binvalue = (select own_resource_uuid from ca_owned_resource) --0x000FD4EB2F811A43A3D674D5FA7B5E95
declare @charvalue varchar(255)
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
Select @charvalue
iget
Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, = or when the subquery is used as an expression.
End result is to join the two tables and match on that cleaned value
--- to be able to join the two tables on the cleaned values and return matched data
SELECT
a.l_persid
,REPLACE(CAST((a.l_persid) AS NVARCHAR(100)),'nr:','')
,b.resource_name
,b.own_resource_uuid
,REPLACE(sys.fn_sqlvarbasetostr(b.own_resource_uuid),'0x','')
from
A a left join B b ON
REPLACE(CAST((a.l_persid) AS NVARCHAR(100)),'nr:','') =
REPLACE(sys.fn_sqlvarbasetostr(b.own_resource_uuid),'0x','')
Where
a.l_attr = 'asset' And a.r_attr = 'chgnr'
July 16, 2008 at 2:02 am
Make it a function as;
CREATE FUNCTION dbo.fnConvertBinaryToChar
(
-- Add the parameters for the function here
@binvalue varbinary(255)
)
RETURNS char(500)
AS
BEGIN
declare @charvalue varchar(255)
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
--Select @charvalue
RETURN @charvalue
END
GO
Then you can call it anywhere as;
Select *,upper(dbo.fnConvertBinaryToChar(own_resource_uuid)) from #res
Or any other way you want to....
Enjoy T-SQL...:)
Atif Sheikh
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply