June 18, 2013 at 8:08 am
Hey guys,
We have this system that is not built for reporting but we are having to write reports for it anyways...
Problem:
All of the following tables are related but the id's are stored in 4 different ways
Table1 - Stored as a varchar...Example: D19377322B06194DB6A9B34667D506A6
Table2 - Stored as a binary...Example: 0xD19377322B06194DB6A9B34667D506A6
Table2 - Stored as nvarchar...Example: {E867D837-EB0C-4EDB-B56E-D7083C181E52}
Table3 - Strred as a uniqueidentifier...Example: E867D837-EB0C-4EDB-B56E-D7083C181E52
I have to get to Table4 to retrieve a column starting with Table1...I can do this manually and here is how:
1.) Get ID from Table1 -D19377322B06194DB6A9B34667D506A6
2.) Manually add 0x to that ID - 0xD19377322B06194DB6A9B34667D506A6
3.) Using that new ID and hard coding it into the WHERE clause, I select a separate id from that table : {E867D837-EB0C-4EDB-B56E-D7083C181E52}
4.) Take the brackets off of that ID - E867D837-EB0C-4EDB-B56E-D7083C181E52
5.) Use that new ID to retrieve the column I need
So doing this manually, no problem!
Problem is there are thousands of these and I cant do it manually one by one! I get stuck on step 3, in the WHERE clause: when specifying a binary, you dont use quotes, but I cant figure out how to get around that? I have tried setting the value using a variable and in the WHERE clause, I just say where ID = @num, but that doesnt work!
So my question is: How can I take a varchar value and make it a binary so I can search on it!
Any confusion as to what I am asking, let me know!
Thanks
June 18, 2013 at 8:18 am
In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 18, 2013 at 9:06 am
You can do the conversions on the fly in a single query by using expressions in your joins and where clauses. It may be slower than joins using the same datatypes, but definitely faster than doing it by hand.
declare @table1 table ( value1 varchar(50) )
insert into @table1 select 'D19377322B06194DB6A9B34667D506A6'
declare @table2 table ( value2 binary(50), value3 nvarchar(50))
insert into @table2 select convert(binary,'0xD19377322B06194DB6A9B34667D506A6'), '{E867D837-EB0C-4EDB-B56E-D7083C181E52}'
declare @table3 table ( value4 uniqueIdentifier)
insert into @table3 select 'E867D837-EB0C-4EDB-B56E-D7083C181E52'
select value1,value2,value3,value4
from @table1 t1
join @table2 t2 on t2.value2 = convert(binary,'0x'+t1.value1)
join @table3 t3 on t3.value4 = substring(t2.value3,2, len(t2.value3)-2)
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
June 18, 2013 at 9:20 am
The Dixie Flatline (6/18/2013)
You can do the conversions on the fly in a single query by using expressions in your joins and where clauses. It may be slower than joins using the same datatypes, but definitely faster than doing it by hand.
declare @table1 table ( value1 varchar(50) )
insert into @table1 select 'D19377322B06194DB6A9B34667D506A6'
declare @table2 table ( value2 binary(50), value3 nvarchar(50))
insert into @table2 select convert(binary,'0xD19377322B06194DB6A9B34667D506A6'), '{E867D837-EB0C-4EDB-B56E-D7083C181E52}'
declare @table3 table ( value4 uniqueIdentifier)
insert into @table3 select 'E867D837-EB0C-4EDB-B56E-D7083C181E52'
select value1,value2,value3,value4
from @table1 t1
join @table2 t2 on t2.value2 = convert(binary,'0x'+t1.value1)
join @table3 t3 on t3.value4 = substring(t2.value3,2, len(t2.value3)-2)
Need to be careful doing conversions of Varchars into Binary.
With the default style, you'll end up with a binary representation of the varchar, not the conversion of the Hex to Binary, which is what you actually want.
Note the differences here:
DECLARE @HexVarchar VARCHAR(32)='D19377322B06194DB6A9B34667D506A6'
SELECT convert(VARBINARY,@HexVarchar)
--0x443139333737333232423036313934444236413942333436363744353036
SELECT convert(VARBINARY,@HexVarchar,2)
--0xD19377322B06194DB6A9B34667D506A6
June 18, 2013 at 9:24 am
Here is one option. I used a special qualifier of the CONVERT() function to get the correct binary value out of the VARCHAR value in the first table.
with
Table1 as
(select ID_VARCHAR = 'D19377322B06194DB6A9B34667D506A6'),
Table2 as
(select ID_BINARY = cast(0xD19377322B06194DB6A9B34667D506A6 as binary(16)),
ID2_NVARCHAR = cast('{E867D837-EB0C-4EDB-B56E-D7083C181E52}' as nvarchar(40))),
Table3 as
(select ID2_GUID = cast('E867D837-EB0C-4EDB-B56E-D7083C181E52' as uniqueidentifier))
select
*
from
Table1 inner join
Table2 on convert(binary(16), Table1.ID_VARCHAR, 2) = Table2.ID_BINARY inner join
Table3 on convert(uniqueidentifier, Table2.ID2_NVARCHAR) = Table3.ID2_GUID
June 18, 2013 at 9:55 am
I stand corrected. Thanks, guys.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
June 18, 2013 at 11:13 am
HowardW (6/18/2013)
The Dixie Flatline (6/18/2013)
You can do the conversions on the fly in a single query by using expressions in your joins and where clauses. It may be slower than joins using the same datatypes, but definitely faster than doing it by hand.
declare @table1 table ( value1 varchar(50) )
insert into @table1 select 'D19377322B06194DB6A9B34667D506A6'
declare @table2 table ( value2 binary(50), value3 nvarchar(50))
insert into @table2 select convert(binary,'0xD19377322B06194DB6A9B34667D506A6'), '{E867D837-EB0C-4EDB-B56E-D7083C181E52}'
declare @table3 table ( value4 uniqueIdentifier)
insert into @table3 select 'E867D837-EB0C-4EDB-B56E-D7083C181E52'
select value1,value2,value3,value4
from @table1 t1
join @table2 t2 on t2.value2 = convert(binary,'0x'+t1.value1)
join @table3 t3 on t3.value4 = substring(t2.value3,2, len(t2.value3)-2)
Need to be careful doing conversions of Varchars into Binary.
With the default style, you'll end up with a binary representation of the varchar, not the conversion of the Hex to Binary, which is what you actually want.
Note the differences here:
DECLARE @HexVarchar VARCHAR(32)='D19377322B06194DB6A9B34667D506A6'
SELECT convert(VARBINARY,@HexVarchar)
--0x443139333737333232423036313934444236413942333436363744353036
SELECT convert(VARBINARY,@HexVarchar,2)
--0xD19377322B06194DB6A9B34667D506A6
I used this and I had tried the CONVERT function before but I was getting that 1st result with the CONVERT...Didnt know or think about putting a style in there! But once I did that, worked to perfection, thanks guys!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply