Reputation: 361
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
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
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
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
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