Convert varchar to uniqueidentifier in SQL Server

Non-standard GUID? No Problem!

Bummed out by a non-standard varchar format? Let's reshape it for conversion.

The Craftsman’s Guide to GUID Correction

Here's the secret sauce to deal with tricky GUIDs that don't fit the mold.

Insert Missing Hyphens with STUFF

Use STUFF to sneak in hyphens at the needed positions in your pesky GUID:

SELECT CAST(STUFF(STUFF(STUFF(STUFF(Your-Varchar-GUID, 9, 0, '-'), 14, 0, '-'), 19, 0, '-'), 24, 0, '-') AS UNIQUEIDENTIFIER)


It's like threading a needle; focus is key! 😉

Removing the Hexed '0x'

Is your GUID coming as a binary hex? Strip the '0x' before casting:

SELECT CAST(SUBSTRING(Your-Varchar-GUID, 3, 32) AS UNIQUEIDENTIFIER)


Hex marks the spot! But sometimes, it's best to clean it up.

Checking Data Quality

Ensure data integrity before it's a pressing issue. No one enjoys surprises during conversions!

Classy one-liners

Beware, elegant one-liners coming through! Quick and tidy:

SELECT CAST(SUBSTRING(Your-Varchar-GUID, 1, 8) + '-' + SUBSTRING(Your-Varchar-GUID, 9, 4) + '-' + SUBSTRING(Your-Varchar-GUID, 13, 4) + '-' + SUBSTRING(Your-Varchar-GUID, 17, 4) + '-' + SUBSTRING(Your-Varchar-GUID, 21, 12) AS UNIQUEIDENTIFIER)


Writing SQL, or poetry? You decide.

Navigating Common Roadblocks

• Avoid invalid characters and length discrepancies.
• Handle cases with null or empty strings with care.

Preserve Data Integrity

Run a validity check post-conversion. Just to be sure!

SELECT IIF(CAST(Your-Varchar-GUID AS UNIQUEIDENTIFIER) IS NOT NULL, 'Valid', 'Invalid') AS GUID_Validity


Did the operation go well, Doc?

Automate with Update Scripts

Make hard work smarter, not just harder. Automate conversions:

-- All in a day's work
UPDATE YourTable 
SET YourUniqueIdColumn = CAST(YourVarcharGUIDColumn AS UNIQUEIDENTIFIER)
WHERE YourVarcharGUIDColumn IS NOT NULL AND LEN(YourVarcharGUIDColumn) = 36


Always backup your files. No one wants a 'whoopsie' with the originals.