# SQL Server Varbinary/Varchar Casting **URL:** \ **Category:** Uncategorized\ **Created:** [November 5, 2018, 10:20pm UTC](https://community.dremio.com/t/sql-server-varbinary-varchar-casting/2184 "2018-11-05T22:20:07Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![akikax](https://avatars.discourse-cdn.com/v4/letter/a/ee7513/32.png) [@akikax](https://community.dremio.com/u/akikax)\ **Post date:** [November 5, 2018, 10:20pm UTC](https://community.dremio.com/t/sql-server-varbinary-varchar-casting/2184/1 "2018-11-05T22:20:07Z")
Hi Everyone, We have two columns: 1. Varbinary (SQL Server Uniqueidentifier) 2. Varchar Is it possible in Dremio to cast one column data type into the other (both directions). If so, how do you do it without changing the content but just the data type? Since SQL does not support implicit casting for these two data types (fair enough) how do you explicitly do it? We gave it a try using the convert\_from/to functions without success. In the image below you can see some example values. We would like to be able to represent the same sequence of characters using varbinary or varchar. Here’s an example of a SQL Server uniqueidentifier value: 5740191A-363C-4FFA-942B-A547131AFDFC ![Casting](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/3/3bdcc5a6b140f1ab6c1ec7bd82b96d8459db66ab.jpeg) ---
**Author:** ![rmason](https://avatars.discourse-cdn.com/v4/letter/r/edb3f5/32.png) [@rmason](https://community.dremio.com/u/rmason)\ **Post date:** [November 20, 2019, 9:51pm UTC](https://community.dremio.com/t/sql-server-varbinary-varchar-casting/2184/2 "2019-11-20T21:51:46Z")
Does anyone has a resolution to this issue? We pull JSON data from Azure Data Lake containing unqueidentifiers. Dremio pulls these values as varchar data types. We want to join this data to data we pull from SQL Server (where uniqueidentifiers are pulled as varbinary). How can we join these two data sources? ---
**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\ **Post date:** [November 21, 2019, 2:33pm UTC](https://community.dremio.com/t/sql-server-varbinary-varchar-casting/2184/3 "2019-11-21T14:33:22Z")
@akikax, Something like convert\_from(column\_name,‘utf8’,‘x’) column\_name should work. Did you get an error when you tried convert\_from? ---
**Author:** ![rmason](https://avatars.discourse-cdn.com/v4/letter/r/edb3f5/32.png) [@rmason](https://community.dremio.com/u/rmason)\ **Post date:** [November 21, 2019, 4:22pm UTC](https://community.dremio.com/t/sql-server-varbinary-varchar-casting/2184/4 "2019-11-21T16:22:22Z")
> [@balaji.ramaswamy](#): > > convert\_from(column\_name,‘utf8’,‘x’) I don’t get an error, but I also don’t get a value that will match across sources. I either need to be able to convert a UniqueIdentifier contained in a VARCHAR field to a BINARY value that will match what Dremio is creating for SQL Server UniqueIdentifier columns, or I need a way to convert the value Dremio returns from a SQL UniqueIdentifier column into a VARCHAR that matches the original UniqueIdentifier string (xxxxxxxx-xxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx). Unless there is a way to modify how Dremio is interpreting a SQL UniqueIdentifier column. I could use a view on the SQL side to cast the UID to a VARCHAR and then pull the data from the view, but then any predicates pushed to SQL by Dremio would not be able to utilize any indexing on the UniqueIdentifier column. ---
**Author:** ![galiats](https://avatars.discourse-cdn.com/v4/letter/g/858c86/32.png) [@galiats](https://community.dremio.com/u/galiats)\ **Post date:** [April 30, 2020, 3:14pm UTC](https://community.dremio.com/t/sql-server-varbinary-varchar-casting/2184/5 "2020-04-30T15:14:29Z")
Is there any solution for this? im using Micosoft SQL server and I cant use the VARBINARY produced from the uniqueidentifier to make basic queries. ---
**Author:** ![galiats](https://avatars.discourse-cdn.com/v4/letter/g/858c86/32.png) [@galiats](https://community.dremio.com/u/galiats)\ **Post date:** [April 30, 2020, 8:07pm UTC](https://community.dremio.com/t/sql-server-varbinary-varchar-casting/2184/6 "2020-04-30T20:07:30Z")
From experimenting with different functions, for the uniqueidentifier ‘A831071E-E183-44D5-B597-0A87F2E3F3F8’ from the sql server dremio get the VARBINARY ‘HgcxqIPh1US1lwqH8uPz+A==’ or this is what i can see in the web ui. If I use the to\_hex function the resulting value is ‘1E0731A883E1D544B5970A87F2E3F3F8’ well if you observe closely it seems that its every character from the original uniqueidentifier expept sufled and with the ‘-’ character missing. ---
**Author:** ![galiats](https://avatars.discourse-cdn.com/v4/letter/g/858c86/32.png) [@galiats](https://community.dremio.com/u/galiats)\ **Post date:** [July 28, 2020, 12:31pm UTC](https://community.dremio.com/t/sql-server-varbinary-varchar-casting/2184/7 "2020-07-28T12:31:34Z")
We solve this issue by implementing our own StringFunction and recompile the Dremio code with it. We simply rearrange the characters in the string and add ‘-’ where is needed. File: StringFunctions.java ``` @FunctionTemplate(name = "to_uuid", scope = FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL) public static class ToUUID implements SimpleFunction { @Param VarBinaryHolder in; @Output VarCharHolder out; @Workspace Charset charset; @Inject ArrowBuf buffer; @Override public void setup() { charset = java.nio.charset.Charset.forName("UTF-8"); } @Override public void eval() { byte[] buf = com.dremio.common.util.DremioStringUtils.toBinaryStringNoFormat(in.buffer.asNettyBuffer(), in .start, in.end).getBytes(charset); java.nio.ByteBuffer byteBuffer = java.nio.ByteBuffer.wrap(buf); Long high = byteBuffer.getLong(); Long low = byteBuffer.getLong(); String uuidOriginal = new String(buf); String uuid = new String(); uuid = uuidOriginal.substring(6, 8) + uuidOriginal.substring(4, 6) + uuidOriginal.substring(2, 4) + uuidOriginal.substring(0, 2) + "-" + uuidOriginal.substring(10, 12) + uuidOriginal.substring(8, 10) + "-" + uuidOriginal.substring(14, 16) + uuidOriginal.substring(12, 14) + "-" + uuidOriginal.substring(16, 20) + "-" + uuidOriginal.substring(20, 32); out.buffer = buffer = buffer.reallocIfNeeded(uuid.getBytes().length); buffer.setBytes(0, uuid.getBytes()); buffer.setIndex(0, uuid.getBytes().length); out.start = 0; out.end = uuid.getBytes().length; out.buffer = buffer; } } ``` ---
**Author:** ![abest](https://avatars.discourse-cdn.com/v4/letter/a/e480ec/32.png) [@abest](https://community.dremio.com/u/abest)\ **Post date:** [August 12, 2020, 8:07pm UTC](https://community.dremio.com/t/sql-server-varbinary-varchar-casting/2184/8 "2020-08-12T20:07:33Z")
Thank you for your response. I don’t have the option to add the function at this time, but your answer led me to the Dremio SQL version of it. Here it is if anyone else needs it. I’m sure it’s very inefficient, but it does the job. My opinion something like this function should be included, or an option to read them like this from the source. Our users are used to seeing the UUID’s in this format, and will be very confused if they don’t look like this. ``` CONCAT(substring(TO_HEX(uuid_column),7, 2), substring(TO_HEX(uuid_column),5, 2), substring(TO_HEX(uuid_column),3, 2), substring(TO_HEX(uuid_column),1, 2), '-', substring(TO_HEX(uuid_column),11, 2), substring(TO_HEX(uuid_column),9, 2), '-', substring(TO_HEX(uuid_column),15, 2), substring(TO_HEX(uuid_column),13, 2), '-', substring(TO_HEX(uuid_column),17, 4), '-', substring(TO_HEX(uuid_column),21, 12)) fixed ```