Search code examples
sqlsql-serverguidadvantage-database-server

How to generate a GUID string in the same format as Advantage Database Server?

I have an advantage database V11 in which I currently store a unique identifier which is a GUID string generated 22 characters long using NewIDString('F').

The snippet from the ADS help is as follows :-

"F or File – A GUID encoded as a 22-byte string using File and URL Safe base64 encoding with the format of xxxxxxxxxxxxxxxxxxxxxx. Base64 encoded strings are case sensitive and should not be stored in case insensitive string fields."

Is it possible to generate the same format GUID string in Microsoft SQL?

If not, the ADS Help file lists other formats as follows :-

NEWIDSTRING Returns a GUID formatted as a string. If the format parameter is not specified, the GUID string is formatted as a hexadecimal string with the following pattern xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx. The format parameter can be the following values:

M or MIME – A GUID encoded as a 24-byte string using MIME base64 encoding with the format of xxxxxxxxxxxxxxxxxxxxxxxx. Base64 encoded strings are case sensitive and should not be stored in case insensitive string fields.

F or File – A GUID encoded as a 22-byte string using File and URL Safe base64 encoding with the format of xxxxxxxxxxxxxxxxxxxxxx. Base64 encoded strings are case sensitive and should not be stored in case insensitive string fields.

N or Numbers – A GUID encoded as a 32-byte hexadecimal string value with a format of xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.

D or Delimited – A GUID encoded as a 32-byte hexadecimal string with a format of xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx.

B or Bracketed – A GUID encoded as a 32-byte hexadecimal string with a format of [xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx]

P or Parenthesis – A GUID encoded as a 32-byte hexadecimal string with a format of (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx)

C or Curlybraces – A GUID encoded as a 32-byte hexadecimal string with a format of {xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}

Can MS-SQL generate one of these?

Regards

Mike

Solution

  • In SQL SerVer the NEWID function creates new GUIDs (the SQL Server data type is called UNIQUEIDENTIFIER). A GUID is a binary value, the various formats you mention in your question are just that, merely different string representations of the binary value. In the MSDN page for NEWID example A shows the standard string representation of a GUID as XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX.