UDF to display string as hexcode

Question

I have some strange characters in a VARCHAR field which prevent me from converting the field into an INTEGER. I want to debug this and so I need an UDF that will convert the string to a hexadecimal code (just like in a hex editor) so that I can see which characters I am dealing with.

Where can I find such a function or how can I write it?

Answer 1

Simple casting to varbinary would do the trick.

select cast( @string as varbinary(max)) 

Answer 2

And if you implement RULE and CHECK CONSTRAINTS in the, um, database, you can prevent strange characters getting into your fields in the first place. It is in the manual.