I have two different tables in my database and I am joining them to extract a varbinary or a varchar depending on which one comes first. I am using the operator coalesce to select between them and also using the convert operator to convert the varchar to varbinary.
The final query is something like this:
select coalesce(t1.varbinary_column, convert(varbinary(max), t2.varchar_column)) form table1 t1 inner join table2 t2....
The query returns a byte array that when converted to a java.lang.String separates each character with a additional space. Is also needed to say that I am using spring data to execute this native query and also using ResultSet to get the extracted data. The question is how can I avoid this behavior?
After analysing the retrieved data I saw that Sql Server was converting automatically the varchar data to varbinary encoded with UTF-16LE. To sove it with the java code, I just nedded to instanciate a String passing the encoding like this:
new String(retrievedByteArray, Charset.forName("UTF-16LE"))