0

I am migrating data from SQL Server to Oracle in a specified format. I have a table in SQL Server that has a column of type hierarchyid. I want to convert it into string, without changing its look. For example, if the hierarchyid type contain 0x78, I want it to convert to string type "0x78". How can I do it? I have the freedom to use SQL Server and/or Java to do this conversion. Thanks in advance.

  • I can help you with that. But first, let me ask you "why do you want to do that?". Specifically, hierarchyid is a CLR datatype and as such, Oracle isn't going to be able to do much with the binary representation of it once you've migrated the data in the way you're describing. That is, if you do that, you're very likely going to lose data. – Ben Thul Sep 26 '19 at 20:10
  • I am creating a data mart. My OLTP is SQL Server and my Data mart is in Oracle. I know how to convert it into meaningful string format using ToString function available in SQL Server. However, I have to keep the source column as well in my data mart so that if anyone wants to verify this column with the source, he/she can do so. –  Sep 26 '19 at 21:11
  • your question may be more suited for [DBA.SE] – Samuel Liew Sep 27 '19 at 04:09

2 Answers2

0

Given the comments above, the most straightforward way to do this is

declare @h HIERARCHYID = '/1/1/1/'

select @h, convert(varchar(50), cast(@h as varbinary), 1)
Ben Thul
  • 31,080
  • 4
  • 45
  • 68
  • Hey Ben, This converts the data in a readable format. I want the data to be converted to string without changing its "look". If the column has value 0x78, I want '0x7x' in string format. Your solution gives the same output as the ToString function. –  Sep 27 '19 at 01:09
  • And that's what I get for posting without testing. I've edited the above to reflect something that' verified as actually working! Sorry about that. – Ben Thul Sep 29 '19 at 04:12
0
declare @hid hierarchyid = '/1/1/1/';

select 
    v1 = convert(varchar(1000), cast(@hid as varbinary(892)), 1), -- = 0x5AD6
    v2 = convert(varchar(1000), cast(@hid as varbinary(892)), 2); -- = 5AD6
Vadim Loboda
  • 2,431
  • 27
  • 44
  • Thanks Vadim. Your solution worked. My input of 0x78 is transformed to '78'. The suffix of '0x' can be concatenated to all rows. Thanks a lot!! –  Sep 27 '19 at 13:49