CODEWITHSUNDEEP
sql-server
Amit
Amit

Reputation: 187

Error converting data type varchar to varbinary in sql only for few values

IF(ISNUMERIC(RTRIM(LTRIM('83B1B88'))) = 0)
select CONVERT(VARCHAR, CONVERT(INT, CONVERT(VARBINARY, CONVERT(VARCHAR, '0x' + '83B1B88'), 1)))

I am not sure why sql is throwing Error converting data type varchar to varbinary I am getting this exception only for values like 1B91B32,169DF90, 13077B5

Can anyone help me out ?

Upvotes: 2

Views: 12164

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

You're passing a style of 1 to CONVERT.

If the data_type is a binary type, the expression must be a character expression. The expression must be composed of an even number of hexadecimal digits (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, A, B, C, D, E, F, a, b, c, d, e, f). If the style is set to 1 the characters 0x must be the first two characters in the expression. If the expression contains an odd number of characters or if any of the characters are invalid an error is raised.

My emphasis.

All of your examples of invalid sequences appear to have odd lengths. You should add a padding 0 if you have an odd length sequence. Whether you pad at the start or the end depends on your exact requirements, where the sequence came from, etc.

Upvotes: 9

Related Questions