Converting VARBINARY to STRING on SQL Server

I recently had a requirement to load some data from a source table to another destination table. The destination columns were exactly the same as the source columns with the same data types and length. The only difference was that some columns on the destination table must be encrypted. The task was to use the SHA2_512 encryption algorithm to encrypt the “sensitive” data. I will talk more about the encryption algorithm in another post.

To achieve this, I needed to use the HASHBYTES function in SQL Server. The challenge was that this function used with the SHA2_512 encryption algorithm will return a fixed character length of 64 characters which will be longer than the character length on my destination table. As a result, SQL Server will throw a truncation error. I will demonstrate this below.

Create and insert some records in the source table

CREATE TABLE [dbo].[Employee]
(
[EmployeeID] INT IDENTITY(1, 1),
[FirstName] [VARCHAR](50) NULL,
[LastName] [VARCHAR](50) NULL,
[BirthDate] [DATE] NULL
) ON [PRIMARY];
GO

INSERT [dbo].[Employee]
(
[FirstName],
[LastName],
[BirthDate]
)
VALUES
(N'John', N'Brown', '1960-01-01'),
(N'Pete', N'Dawn', '1965-12-03'),
(N'Amanda', N'Lopez', '1980-06-01'),
(N'Adam', N'Smith', '1971-02-26'),
(N'Taylor', N'Ramsey', '1984-03-01'),
(N'John', N'Brown', '1960-01-01'),
(N'Pete', N'Dawn', '1965-12-03'),
(N'Amanda', N'Lopez', '1980-06-01'),
(N'Adam', N'Smith', '1971-02-26'),
(N'Taylor', N'Ramsey', '1984-03-01'),
(N'John', N'Brown', '1960-01-01'),
(N'Pete', N'Dawn', '1965-12-03');

Create the destination table

CREATE TABLE [dbo].[Employee_PII]
(
[EmployeeID] INT IDENTITY(1, 1),
[FirstName] [VARCHAR](20) NULL,
[LastName] [VARCHAR](20) NULL,
[BirthDate] [DATE] NULL
) ON [PRIMARY];
GO

For the purpose of this demonstration, I will be encrypting the FirstName and LastName columns

SELECT EmployeeID,
HASHBYTES('SHA2_512', FirstName) AS FirstName,
HASHBYTES('SHA2_512', LastName) AS LastName,
BirthDate
FROM dbo.Employee;

The length of the encrypted fields are 64 characters long. I will attempted to insert this into the Employee_PII table as is.

INSERT dbo.Employee_PII
(
FirstName,
LastName,
BirthDate
)
SELECT HASHBYTES('SHA2_512', FirstName) AS FirstName,
HASHBYTES('SHA2_512', LastName) AS LastName,
BirthDate
FROM dbo.Employee;

As expected, SQL returned a string truncation error message

Using string functions – LEFT() and SUBSTRING()

I attempted using the LEFT() and SUBSTRING() string functions. But since the HASHBYTES function returns a VARBINARY data type, the results were not what I expected

LEFT() Function

SELECT LEFT(HASHBYTES('SHA2_512', FirstName), 50) AS FirstName,
LEFT(HASHBYTES('SHA2_512', LastName), 50) AS LastName,
BirthDate
FROM dbo.Employee;

The SUBSTRING() function also produced similar result.

So how do we get this to work?

Using the CONVERT() function

This function is used to convert an expression of one data type to another. You can read more about the function here.

The syntax is CONVERT ( data_type [ ( length ) ] , expression [ , style ] ).

The style argument is optional. So, let’s try using CONVERT() and see what it returns.

SELECT CONVERT(VARCHAR(50), HASHBYTES('SHA2_512', FirstName)) AS FirstName,
CONVERT(VARCHAR(50), HASHBYTES('SHA2_512', LastName)) AS LastName,
BirthDate
FROM dbo.Employee;

You will observe that it returns the same result as using the LEFT() and SUBSTRING() functions.

To get our desired result, the trick is to use the optional style parameter on the CONVERT() function. The value can be 0, 1 or 2. 0 is the default and will yield the same result similar to the one above. Using a 1 or 2 will truncate the result (which is what we want) if the length of the converted expression exceeds the data type length. You can read up more on this on the Microsoft docs.

Setting the style value to 1

SELECT CONVERT(VARCHAR(50), HASHBYTES('SHA2_512', FirstName), 1) AS FirstName,
CONVERT(VARCHAR(50), HASHBYTES('SHA2_512', LastName), 1) AS LastName,
BirthDate
FROM dbo.Employee;

This yields the desired result and you will notice that the characters 0x are added to the left of the converted result

Setting the style value to 2

SELECT CONVERT(VARCHAR(50), HASHBYTES('SHA2_512', FirstName), 2) AS FirstName,
CONVERT(VARCHAR(50), HASHBYTES('SHA2_512', LastName), 2) AS LastName,
BirthDate
FROM dbo.Employee;

This also returns the desired result except that the characters 0x are not added to the left of the converted result

Inserting the converted result into the destination table

INSERT dbo.Employee_PII
(
FirstName,
LastName,
BirthDate
)
SELECT CONVERT(VARCHAR(50), HASHBYTES('SHA2_512', FirstName), 2) AS FirstName,
CONVERT(VARCHAR(50), HASHBYTES('SHA2_512', LastName), 2) AS LastName,
BirthDate
FROM dbo.Employee;

This should insert successfully.

Let’s check the destination table

SELECT *
FROM dbo.Employee_PII;

Summary

We often overlook the optional style parameter on the CONVERT() function and limit it to just changing date styles. As shown in the above demonstration, we can see that this parameter, although optional, can be very powerful.