UUID in hyphen separated representation in SAP HANA

[iperez-sofos]

Greetings.

I need to convert a UUID from RAW16 format to hyphenated representation, or better put another way, I need to generate and insert into a table a UUID which must be in hyphen separated format.

1. So far I have UUID fields like this: 0050569A14531ED5B9CB7E54178C13F0

2. I need the inserted value to be as follows: 0050569a-1453-1ed5-b9cb-7e54178c13f0.

I explain the situation in greater depth:

I am working on a project developed in SAP HANA Express and SAP CAP. Despite using SAP CAP, most of the project functionalities have been programmed at the Database level (using SQLScript).

In that context, I have a table that is exposed in an SAP CAP service with the @readonly annotation (that is, it can only be queried with HTTP GET requests). The insertion of data in that read-only table is done in a BEFORE INSERT trigger of another table (table which is write-only, that is, annotation @insertonly).

The UUID of the first table (table @readonly) I create using SYSUUID or NEWUID as part of a SQL INSERT INTO statement. This UUID is created in RAW16 format (or the format that I showed in 1). However, when I try to query (GET requests) the @readonly table, I receive an error message indicating that there is a problem deserializing the UUID.

I understand or think I understand (even from the error message itself) that I need to change the format of the UUID to hyphen separated representation.

Is it possible to do what I am looking for?

How can I do this? (Especially at the SQLScript level, since the handling of the @readonly table is at that level).

Thankful in advance for any help and/or suggestions.

[stefan_faust]

Hi Isaac,

I know you had asked this question already last December, but I was just having the same requirement. If you are using the SYSUUID function, you could wrap it in further function that modify it:

SELECT 
    LOWER( 
        REPLACE_REGEXPR('([0-9A-F]{8})([0-9A-F]{4})([0-9A-F]{4})([0-9A-F]{4})([0-9A-F]{12})' FLAG 'i' 
                     IN SYSUUID WITH '\1-\2-\3-\4-\5')
    )
FROM DUMMY;

The code does not check if it is a valid UUID. It simply expects to get a 32 char string as input. It is split up and transformed with REPLACE_REGEXPR, which cuts the string into five capturing groups spanning the expected number of characters for each segment between the dash characters in the 36 char string. The flag i is used to ensure that in case there will ever be a change to lowercase output from SYSUUID, the statement does not break.

LOWER is used to convert the result to lower case as was your output string.

This should result in getting the UUID formatted as required. I tested this via SQL Console in BTP Hana as a Service DB.

BR, Stefan