Sign in Register
Go to solution
Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Rosie Holliday 4 posts 34 karma points
    Nov 10, 2020 @ 16:01
    Rosie Holliday
    0

    Umbraco UDI to nodeid in SQL

    Hi all,

    I'm currently interrogating an Umbraco 8 SQL database for Power BI reasons.

    I am able to get so far with my query, however I am hitting a block where one of the properties is populated using a picker. The resulting column data is an Umbraco UDI (umb://document/stringifiedguid).

    Is there any way to turn this back in to a node ID without needing to use the Umbraco helper (getIdforUdi) in C# as this really needs to just be an SQL query.

    Thanks in advance for any help.

    Copy Link
  • Joep 96 posts 698 karma points
    Nov 11, 2020 @ 15:58
    Joep
    100 
    DECLARE @uuid VARCHAR(50)
set @uuid = PARSENAME(REPLACE('umb://document/stringifiedguid', '/', '.'), 1)

select id from umbracoNode
where uniqueId =CAST(
        SUBSTRING(@uuid, 1, 8) + '-' + SUBSTRING(@uuid, 9, 4) + '-' + SUBSTRING(@uuid, 13, 4) + '-' +
        SUBSTRING(@uuid, 17, 4) + '-' + SUBSTRING(@uuid, 21, 12)
        AS UNIQUEIDENTIFIER)

    This will select the Int node id. Just change the 'umb://document/stringifiedguid' to the real version and it should return the correct id.

    -Joep

    Copy Link
  • Rosie Holliday 4 posts 34 karma points
    Nov 11, 2020 @ 16:00
    Rosie Holliday
    0

    Brilliant! Thank you so much!!

    Copy Link
  • Rosie Holliday 4 posts 34 karma points
    Nov 11, 2020 @ 16:10
    Rosie Holliday
    0

    I wanted to add my own solution too, in case it helps anyone in the future

    SELECT id 
    FROM umbracoNode 
    where REPLACE(uniqueId, '-', '') =  REPLACE('umb://document/stringifiedguid', 'umb://document/', '')
    Copy Link
Please Sign in or register to post replies

Write your reply to:

Draft