Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Searching on Binary data type columns Expand / Collapse
Author
Message
Posted Tuesday, June 18, 2013 8:08 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 3:01 PM
Points: 187, Visits: 423
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

Post #1464675
Posted Tuesday, June 18, 2013 8:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:13 AM
Points: 13,328, Visits: 12,821
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1464685
Posted Tuesday, June 18, 2013 9:06 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 2,787, Visits: 6,098
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? -- Stephen Stills
Post #1464720
Posted Tuesday, June 18, 2013 9:20 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 3, 2014 8:05 AM
Points: 1,191, Visits: 9,892
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

Post #1464728
Posted Tuesday, June 18, 2013 9:24 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 21, 2014 7:49 PM
Points: 171, Visits: 501
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

Post #1464731
Posted Tuesday, June 18, 2013 9:55 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 2,787, Visits: 6,098
I stand corrected. Thanks, guys.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills
Post #1464755
Posted Tuesday, June 18, 2013 11:13 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 3:01 PM
Points: 187, Visits: 423
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!
Post #1464799
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse