Binary data convert - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Binary data convert

Author

Topic

DaveS166
Starting Member

1 Post

Posted - 2011-01-07 : 16:51:41

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-01-07 : 17:30:15

It may not be obvious, but converting the binary to a char is not "toh". It's "toh" and 5 more CHAR(0) characters. Here is some code to show that:

SELECT '[' + Convert(char(8), 0x746F680000000000, 0) + ']'
SELECT REPLACE('[' + Convert(char(8), 0x746F680000000000, 0) + ']', CHAR(0), '_')
SELECT '[' + CAST('toh'  AS char(8)) + ']'
SELECT REPLACE('[' + CAST('toh'  AS char(8)) + ']', CHAR(0), '_')
SELECT REPLACE('[' + Convert(char(8), 0x746F680000000000, 0) + ']', CHAR(0), '')

So, you can replace the CHAR(0) with a blank/empty string and they will then equal eath other:


SELECT CASE WHEN REPLACE(Convert(char(8), 0x746F680000000000, 0), CHAR(0), '') = CAST('toh'  AS char(8)) THEN 1 ELSE 0 END

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-01-07 : 17:37:18

Also, it appears some people have had other issues with CHAR(0). I'm not sure if this link will help for your particular situation or not (changing collation).
http://weblogs.sqlteam.com/peterl/archive/2009/08/23/CHAR0-is-not-that-innocent-you-may-think.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources