CODEWITHSUNDEEP
sql-serverguid
Tetiana
Tetiana

Reputation: 361

Short guid in SQL Server / converting from a character string to uniqueidentifier

I need to create a column witch will contain short guid. So I found out something like this:

alter table [dbo].[Table]
add InC UNIQUEIDENTIFIER not null default LEFT(NEWID(),6)

But I get the error:

Conversion failed when converting from a character string to uniqueidentifier.

I've been trying

LEFT(CONVERT(varchar(36),NEWID()),6)

and

CONVERT(UNIQUEIDENTIFIER,LEFT(CONVERT(varchar(36),NEWID()),6))

But I am still getting the same error.

Upvotes: 3

Views: 14298

Answers (4)

BQF
BQF

Reputation: 105

I read all the answers and used "CrimsonMoose's" answer to solve my problem. After modifying their codes and making them usable, I came up with the following codes :

CREATE OR ALTER VIEW GUIDView AS SELECT [GUID]=NEWID()
GO
CREATE OR ALTER FUNCTION dbo.CreateCompressedGUID()
RETURNS VARCHAR(11)
BEGIN
    DECLARE @result         VARCHAR(22) = '';
    DECLARE @keyGUID        BIGINT;
    DECLARE @resultChars    VARCHAR(70) = 'abcdefghijklmnopqrstuvwxyz0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
    DECLARE @division       INT = LEN(@resultChars)
    DECLARE @divisionRemained int;
    SELECT @keyGUID=CAST(CAST(REVERSE([GUID]) AS VARBINARY(MAX)) AS BIGINT) FROM GUIDView

    WHILE (@keyGUID <> 0)
    BEGIN 
        SET @divisionRemained = @keyGUID % @division;
        SET @divisionRemained = @divisionRemained + 1;
        SET @keyGUID = @keyGUID / @division;
        SET @result = SUBSTRING(@resultChars,@divisionRemained,1) + @result;
    END;

    RETURN @result;
END;
GO
SELECT Example = dbo.CreateCompressedGUID()

Upvotes: 0

g2server
g2server

Reputation: 5367

Using CRYPT_GEN_RANDOM instead of NEWID can improve random distribution of the string.

SELECT LEFT(CAST(CAST(CRYPT_GEN_RANDOM(16) AS UNIQUEIDENTIFIER) AS VARCHAR(50)), 6)

Upvotes: 2

CrimsonMoose
CrimsonMoose

Reputation: 11

I just made this one since I couldn't find a good answer on the internet.

Please keep in mind this is a 64 bit representation of a 128bit value, so it has twice the collision possibilities that a real GUID would have. Does not handle 0.

Function takes a NEWID value: 6A10A273-4561-40D8-8D36-4D3B37E4A19C

and shortens it to : 7341xIlZseT

DECLARE @myid uniqueidentifier= NEWID()
select @myid
DECLARE @bigintdata BIGINT = cast(cast(reverse(NEWID()) as varbinary(max)) as bigint)
DECLARE @charSet VARCHAR(70) = '1234567890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
DECLARE @cBase int = LEN(@charSet)
DECLARE @sUID varchar(22) = ''
DECLARE @x int

WHILE (@bigintdata <> 0)
BEGIN 
    SET @x = CAST(@bigintdata % @cBase as INT) + 1
    SET @bigintdata = @bigintdata / @cBase
    SET @sUID = SUBSTRING(@charSet, @x, 1) + @sUID;
END

SELECT @sUID

Upvotes: 1

krivtom
krivtom

Reputation: 24916

There is no such thing as "short guid". Guid, or uniqueidentifier is a 16 byte data type. You can read about it in MSDN. It means that the length must always be 16 bytes and you cannot use 6 characters as you are trying to do.

In the same MSDN article you can find description how you can initialize this type:

A column or local variable of uniqueidentifier data type can be initialized to a value in the following ways:

  • By using the NEWID function.
  • By converting from a string constant in the form xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, in which each x is a
    hexadecimal digit in the range 0-9 or a-f. For example,
    6F9619FF-8B86-D011-B42D-00C04FC964FF is a valid uniqueidentifier
    value.

In your case you are trying to convert only 6 characters to uniqueidentifier which obviously fails.

If you want to use just 6 characters, just use varchar(6):

alter table [dbo].[Table]
add InC varchar(6) not null default LEFT(NEWID(),6)

Keep in mind that in this case this guid is not guaranteed to be unique.

Upvotes: 6

Related Questions