Sign in Register
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 90 posts 688 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

  • This forum is in read-only mode while we transition to the new forum.

    You can continue this topic on the new forum by tapping the "Continue discussion" link below.

Please Sign in or register to post replies