April 25, 2018 at 8:06 am
Hi All,
Having some issues with a hashing and matching based on a file.
We have a hashed column in a table stored as varbinary and also have a csv file with matching hashed values.
when i perform the query below i get a result
Select HashedColumn
From HashedTable
where HashedColumn = 0x565asd8995asda543454278
however when i load the csv file into the DB I cannot store as varbinary, so stored as nvarchar.
when i attempt the join:
Select HashedColumn
From HashedTable Htbl
Join CSVHashedFile Csv on Htbl.HashedColumn = Csv.HashedColumn
I get no results.
I cannot import the csv as varbinary (DT_Image) in SSIS so am pretty much stuck.
Has anyone else encountered the same issue?
April 25, 2018 at 9:13 am
When you join you'll have to CONVERT the nvarchar value to varchar and then to varbinary.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 8, 2018 at 11:44 am
"however when i load the csv file into the DB I cannot store as varbinary, so stored as nvarchar.
when i attempt the join:"
Why can't you store as varbinary? When loading anything to DB from text files I always load to a staging table first so I can prep the data any way I want before inserting into actual production tables.
May 14, 2018 at 2:42 pm
waxb18 - Wednesday, April 25, 2018 8:06 AMHi All,Having some issues with a hashing and matching based on a file.
We have a hashed column in a table stored as varbinary and also have a csv file with matching hashed values.
when i perform the query below i get a result
Select HashedColumn
From HashedTable
where HashedColumn = 0x565asd8995asda543454278however when i load the csv file into the DB I cannot store as varbinary, so stored as nvarchar.
when i attempt the join:
Select HashedColumn
From HashedTable Htbl
Join CSVHashedFile Csv on Htbl.HashedColumn = Csv.HashedColumnI get no results.
I cannot import the csv as varbinary (DT_Image) in SSIS so am pretty much stuck.Has anyone else encountered the same issue?
You've got a problem if you are expecting a varbinary value to match a string of hex characters where you're using the letter s. That's not a valid binary string character. Only 0-9 and a-f or A-F are valid. Your string was posted as "0x565asd8995asda543454278", which has two occurrences of the letter s.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply