Convert varchar to uniqueidentifier in SQL Server

Converting a varchar value to a uniqueidentifier in SQL Server is one of the common tasks when you are working with GUIDs stored as strings. This process will make sure of the data compatibility and enables you to use the uniqueidentifier data type for operations such as indexing and comparisons.

In this blog, we are going to learn more about the methods to do this conversion using the CONVERT and CAST functions and highlight the key considerations while doing so.

Table of Contents

Ways to convert varchar to uniqueidentifier.

1. Using the CAST function:

The CAST function allows you to convert a varchar to a unique identifier, if the string that you are using is a valid GUID.

DECLARE @guidString VARCHAR(50) = '550e8400-e29b-41d4-a716-446655440000';  
DECLARE @guidValue UNIQUEIDENTIFIER;  

SET @guidValue = CAST(@guidString AS UNIQUEIDENTIFIER);  
SELECT @guidValue;

2. Using the CONVERT Function

The CONVERT function works in a similar way as CAST, but the difference is, it allows you to have more control over the formatting.

DECLARE @guidString VARCHAR(50) = '550e8400-e29b-41d4-a716-446655440000'; 

SELECT CONVERT(UNIQUEIDENTIFIER, @guidString) AS ConvertedGUID;

3. Handling the Invalid Data

If the varchar value is not a valid GUID, then the conversion is going to throw an error. Now, in order to handle similar cases, you can use the TRY_CAST or TRY_CONVERT functions.

DECLARE @guidString VARCHAR(50) = 'invalid-guid'; 

SELECT TRY_CAST(@guidString AS UNIQUEIDENTIFIER) AS ConvertedGUID;

— Output: NULL 

Example

1. Implementation

Now, let us understand everything with the help of an example. Imagine you have a table Employees with a column EmployeeID which is stored as varchar, and  you are required to convert it to uniqueidentifier.

CREATE TABLE Employees (

    EmployeeID TEXT,  -- Store UUIDs as TEXT

    Name NVARCHAR(100)

);

INSERT INTO Employees (EmployeeID, Name)

VALUES

('550e8400-e29b-41d4-a716-446655440000', 'John Doe'),

('123e4567-e89b-12d3-a456-426614174000', 'Jane Smith');

SELECT

    EmployeeID,

    Name

FROM Employees;

-- Example with validation (using GLOB)

SELECT

    EmployeeID,

    Name

FROM Employees

WHERE EmployeeID GLOB '[0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F]-[0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F]-[0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F]-[0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F]-[0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F]';

2.  Handling during Conversion

Now, suppose your table contains invalid GUIDs, use  TRY_CAST or TRY_CONVERT to avoid any runtime errors.

-- Identify invalid GUIDs  
SELECT EmployeeID  
FROM Employees  
WHERE TRY_CAST(EmployeeID AS UNIQUEIDENTIFIER) IS NULL;

Common Problems And Best Practices

1. Best Practices that you can follow

  • Validate your Input: Make sure to always validate your varchar values, which ensures that they follow the GUID format before conversion.
  • Use TRY_CAST or TRY_CONVERT: You should use these functions as they prevent runtime errors when you are working with invalid data.
  • Indexing: After you are done converting to a unique identifier, you should consider adding indexes for better performance.

2. Common Problems:

  • Invalid GUID strings: Attempting to convert invalid GUIDs will result in giving you errors unless you handle it with TRY_CAST.
  • Case Sensitivity: GUIDs are case-insensitive, but the varchar representation might include your mixed cases.

Why do you need to Convert varchar to uniqueidentifier?

Common circumstances:

  • When you want to migrate the legacy database that stores the GUIDs as strings (varchar).
  • When there is a requirement for optimizing the queries by using the uniqueidentifier for the indexing and comparisons.
  • Making sure of the data consistency and preventing invalid GUIDs.

Pros of using uniqueidentifier:

  • The performance is improved due to the GUID operations.
  • It gives the validation at the database level for the valid GUID formats.
  • It decreases the storage requirements compared to storing GUIDs as varchar.

Conclusion

Converting a varchar to a unique identifier in SQL Server is a straightforward process when the data complies with the GUID format. You can use functions such as CAST, CONVERT, and their safer alternatives (TRY_CAST, TRY_CONVERT), and you can easily transform your data while avoiding any errors. This ensures optimal performance and consistency while you are working with GUIDs in your database.

About the Author

Data Engineer

As a skilled Data Engineer, Sahil excels in SQL, NoSQL databases, Business Intelligence, and database management. He has contributed immensely to projects at companies like Bajaj and Tata. With a strong expertise in data engineering, he has architected numerous solutions for data pipelines, analytics, and software integration, driving insights and innovation.

business intelligence professional