January 7, 2011 at 2:59 pm
I have a binary(8) field that I am trying to get data out of. The field value is 0x746F680000000000. When I convert this value I get "toh". When I compare that to 'toh' it doesn't equal.
select case when Convert(char(8), 0x746F680000000000, 0) = 'toh' then 1 else 0 end This yields a 0.
When I type in Len(Convert(varchar(8),0x746F680000000000,0)) OR DataLength(Convert(varchar(8),0x746F680000000000,0)) I get 8. But when I Len('toh') i get 3. How can I have TSql look at 0x746F680000000000 and tell me it's only 3 characters long. Len and DataLength both say 8. IF I can find the length of the valid characters, I can change field length for varchar to the valid character length.
Thanks for any assistance you provide.
Dave
January 7, 2011 at 3:06 pm
because the binary conversion doesn't equate to just three characters.
declare @blah varBinary(8)
SET @blah = 0x746F680000000000
print @blah
print CONVERT( CHAR(8), @Blah, 0) -- Not sure why the 0 but figured I'd test it.
print convert( char(8), @blah)
PRINT CASE WHEN convert( char(8), @blah) = 'toh' THEN 1 ELSE 0 END
This returns a series of unprintable characters after toh (five squares on my screen). Varchar doesn't help here either.
Now, you could try to replace whatever character this is ending up as, but this is telling me it's a char(0):
PRINT ASCII( substring( convert( varchar(8), @blah), 4, 1))
So, trying this:
print REPLACE( convert( varchar(8), @blah), CHAR(0), '')
removes the extra chars... resulting in this:
PRINT CASE WHEN REPLACE( convert( varchar(8), @blah), CHAR(0), '') = 'toh' THEN 1 ELSE 0 END
Which now produces a 1.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply