Rayharwood at 18:50, 22 October 2013

2013-10-22T18:50:20Z

Rayharwood: Created page with "There are several formats acceptable for GUIDs, but SQL only accepts "D-Format" GUIDs when inserting into the database. If you have a 32 character hex string (an "N-Format" G..."

2013-10-22T18:45:07Z

Created page with "There are several formats acceptable for GUIDs, but SQL only accepts "D-Format" GUIDs when inserting into the database. If you have a 32 character hex string (an "N-Format" G..."

New page

There are several formats acceptable for GUIDs, but SQL only accepts "D-Format" GUIDs when inserting into the database.

If you have a 32 character hex string (an "N-Format" GUID) and need to insert it into a UniqueIdentifer column, here's a function you can create:

USE [MyDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Ray Harwood
-- Create date: 10/22/2013
-- Description: Takes N-formatted GUID string (32 hex characters) and creates D-Formatted GUID
-- See http://msdn.microsoft.com/en-us/library/97af8hh4.aspx
-- Also http://stackoverflow.com/questions/1390109/convert-varchar-to-uniqueidentifier-in-sql-server
-- =============================================
CREATE FUNCTION [dbo].[GUID_fromString_N]
(
-- Add the parameters for the function here
@GString varchar(50)
)
RETURNS uniqueidentifier
AS
BEGIN
-- Declare the return variable here
DECLARE @GUID uniqueidentifier
-- Add the T-SQL statements to compute the return value here
SELECT @GUID = CAST(
SUBSTRING(@GString, 1, 8) + '-' + SUBSTRING(@GString, 9, 4) + '-' + SUBSTRING(@GString, 13, 4) + '-' +
SUBSTRING(@GString, 17, 4) + '-' + SUBSTRING(@GString, 21, 12)
AS UNIQUEIDENTIFIER)
-- Return the result of the function
RETURN @GUID
END
GO

Then, when doing an insert, use the function in a manner similar to this:

← Older revision		Revision as of 18:50, 22 October 2013
Line 37:		Line 37:

	Then, when doing an insert, use the function in a manner similar to this:		Then, when doing an insert, use the function in a manner similar to this:
		+
		+	INSERT INTO [MyDatabase].[dbo].[DestinationTable]
		+	([GoodGUID]
		+	,[OtherInfo])
		+	select
		+	[MyDatabase].dbo.GUID_fromString_N(S.[StringKeyField])
		+	,'Oh yeah, we did it!'
		+	FROM [MyDatabase].[dbo].[SourceTable] S

GUID fromString N - Revision history

Rayharwood at 18:50, 22 October 2013

Rayharwood: Created page with "There are several formats acceptable for GUIDs, but SQL only accepts "D-Format" GUIDs when inserting into the database. If you have a 32 character hex string (an "N-Format" G..."