By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,266 Members | 1,297 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,266 IT Pros & Developers. It's quick & easy.

SQL Query Help - Convert string to byte array or copy string data to another table?

P: 3
Hello, I have some varchar(2000) fields in my tables, and they have a lot of "weird" characters in them including line break and carriage returns, etc. When I do a select, I see the entire string of characters. When I pull them from a custom program, dts wizard, or just copy them to the clipboard, it truncates some of them. I'm guessing because it sees a line break or carriage return so it stops there. The result is that the extracted value is not the same as the original.

1. Is there a way to do a Select that converts the string to a byte array or similar so that I get the ascii value of every character?

2. Or is there a way to copy those values from one table to another? I've tried DTS Wizard, and it truncates the values.
Sep 10 '08 #1
Share this Question
Share on Google+
2 Replies


P: 3
What you're asking is pretty simple. In SQL just call the Convert function

convert(binary,YourColumnNameHere,0)
Sep 10 '08 #2

P: 3
That will work, but it'll give you a fixed byte array of 30...so, if you have more or less characters than that you should use varbinary. In your case, you say varchar(2000), so you need to use varbinary(2000) or you won't get all the data. And, it'll use 2000 as the max, so if your varchar is less than 2000, it'll return that number of byte array or ascii values.

convert(varbinary(2000),YourColumnNameHere,0)
Sep 10 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.