How to Efficiently Convert VARCHAR UUIDs to UNIQUEIDENTIFIERs in SQL Server?

SQL Server: Efficiently Transforming VARCHAR UUIDs to UNIQUEIDENTIFIERs

Some databases store UUIDs (Universally Unique Identifiers) as VARCHAR, creating compatibility issues when working with .NET GUIDs which require the UNIQUEIDENTIFIER data type. Direct conversion using CAST or CONVERT often fails due to the missing hyphens in the VARCHAR representation.

This solution provides a streamlined SQL Server method to convert these VARCHAR UUIDs to the correct UNIQUEIDENTIFIER format:

<code class="language-sql">DECLARE @uuid VARCHAR(50)
SET @uuid = 'a89b1acd95016ae6b9c8aabb07da2010'
SELECT CAST(
    SUBSTRING(@uuid, 1, 8) + '-' + SUBSTRING(@uuid, 9, 4) + '-' + SUBSTRING(@uuid, 13, 4) + '-' +
    SUBSTRING(@uuid, 17, 4) + '-' + SUBSTRING(@uuid, 21, 12)
    AS UNIQUEIDENTIFIER)</code>

Here's how this query works:

1. A VARCHAR variable @uuid is declared and initialized with a sample UUID string.
2. The SUBSTRING function extracts the necessary portions of the UUID string.
3. The extracted segments are concatenated using hyphens to reconstruct the standard UUID format.
4. Finally, CAST converts the correctly formatted string into a UNIQUEIDENTIFIER.

This approach avoids schema changes to the original table and keeps the conversion process entirely within SQL Server, improving efficiency.

The above is the detailed content of How to Efficiently Convert VARCHAR UUIDs to UNIQUEIDENTIFIERs in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!