How to Convert a Hyphenless VARCHAR to a UNIQUEIDENTIFIER in SQL Server?

Converting Hyphenless VARCHAR to UNIQUEIDENTIFIER in SQL Server

Problem: Directly converting a VARCHAR column containing hyphen-less unique identifiers to the UNIQUEIDENTIFIER data type in SQL Server often results in errors. Standard conversion techniques fail due to the missing hyphens.

Solution: This efficient SQL query solves the problem by manually inserting the hyphens into the VARCHAR string before casting:

<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>

How it Works: The query extracts substrings from the input VARCHAR (@uuid), strategically placing hyphens to reconstruct the standard UNIQUEIDENTIFIER format (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx). The resulting string is then cast to the UNIQUEIDENTIFIER data type. This method ensures accurate conversion even when dealing with hyphen-less input.

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