DB2: Function to CAST character to varchar (UUID)

1

Hello everyone,

I need to create a lot of views for several tables from DB2. Since I need to perform joins, I want to align the IDs in their format.

Unfortunately some ID columns use the “character” data format. Leading to values like this: “s7U2gcCoStqrJuz1JsWCKQ==”

Currently I convert these values manually like this.

SELECT CONCAT(
        SUBSTR(LOWER(HEX(ID)), 1, 8), '-', 
        SUBSTR(LOWER(HEX(ID)), 9, 4), '-', 
        SUBSTR(LOWER(HEX(ID)), 13, 4), '-', 
        SUBSTR(LOWER(HEX(ID)), 17, 4), '-', 
        SUBSTR(LOWER(HEX(ID)), 21)
    ) AS UUID
FROM 
    "wikis".WIKIS."MEDIA_REVISION"

Resulting in this: “b3b53681-c0a8-4ada-ab26-ecf526c5822”

Is it possible to create a function to make things a little easier/faster? Like this:

SELECT characterToUUID(ID) AS UUID
FROM 
    "Uniconnect_wikis".WIKIS."MEDIA_REVISION"

Really appreciate your support.

Regards

2

Try building your own function using UDFs? User-Defined Functions | Dremio Documentation

3

Hey @lenoyjacob,

thanks for the advice!

My solution currently looks like this:

CREATE FUNCTION hexToUUID(hexValue VARBINARY)
RETURNS VARCHAR
RETURN  CONCAT(SUBSTR(LOWER(HEX(hexValue)), 1, 8), '-', 
        SUBSTR(LOWER(HEX(hexValue)), 9, 4), '-', 
        SUBSTR(LOWER(HEX(hexValue)), 13, 4), '-', 
        SUBSTR(LOWER(HEX(hexValue)), 17, 4), '-', 
        SUBSTR(LOWER(HEX(hexValue)), 21))

Is it possible to create a variable for “LOWER(HEX(hexValue))” instead of converting the original value five times?

regards
Muffex

4

No variables yet in UDFs AFAIK…

But you can probably just do the conversion before calling the function. Something like this:

CREATE OR REPLACE FUNCTION hexToUUID(hexValue VARBINARY)
RETURNS VARCHAR
RETURN  CONCAT(SUBSTR(hexValue, 1, 8), '-', 
        SUBSTR(hexValue, 9, 4), '-', 
        SUBSTR(hexValue, 13, 4), '-', 
        SUBSTR(hexValue, 17, 4), '-', 
        SUBSTR(hexValue, 21))
;

And call it by by using LOWER(HEX(x)):

SELECT hexToUUID(LOWER(HEX(hexValue)))
FROM 
    forum052024
;

Another potential way is to use a subquery in the UDF to do the conversion.

1 Like
5

Thanks again. This is the way.