December 5, 2006 at 11:25 pm
Hi All,
I am having one field in my database table with datatype nvarchar(20).I have to insert unique value in that field (somewhat parrallel to guid or newid()).Currently i am able to achieve that by writing a function on asp.net page through which i am able to generate unique id having width 20.Unique id generation logic contains conversion of integer to hexadecimal and i also added year,week,day,hour,minutes,seconds,milliseconds so that it should be unique.
It is working fine on asp.net page now i need to replicate this logic on database side.I am able to get done everything but not able to find any function or that sort of thing to convert a integer value to hexadecimal.Rest of the thing i can workout.
Another constraint is that i can't use newid or guid as probably it needs uniqueidentifier datatype although i guess we can insert newid into nvarchar datatype by increasing it's width but also i can't change the width.It should be nvarchar(20) only.
I need some way around of converting integer value to hexadecimal with keeping in note of above mentioned restrictions.
Thanks
Madhusudan.
December 6, 2006 at 2:28 am
you can used the varbinary datatype for this purpose.
select CAST(CAST((RAND()*255) AS Int) AS VarBinary(2))
have a look on this and I think it will sortout your problem
cheers
cheers
December 7, 2006 at 7:34 am
declare @IntVal integer
declare @HexVal varbinary(4)
set @IntVal = 212
select @HexVal = convert(varbinary(4), @IntVal)
select @HexVal
December 7, 2006 at 11:15 am
Unfortunately, casting to varbinary and then to string won't work as desired. In this case, "hexidecimal" means a string representation of the decimal value. Try the following function, which you can tweak as necessary. If you uncomment the @padlength code, it will left-pad hex strings shorter than @padLength with zeros.
CREATE FUNCTION dbo.fnInt2Hex
(
@num bigint
--, @padLength int = NULL
)
RETURNS varchar(20)
AS
BEGIN
DECLARE @rem bigint, @base bigint
DECLARE @hexdigits varchar(16)
DECLARE @hex varchar(20)
IF @num = 0
RETURN '0'
SET @base = 16
SET @hexdigits = '0123456789ABCDEF'
SET @hex = ''
WHILE @num <> 0
BEGIN
SET @rem = @num % @base
SET @num = @num / @base
SET @hex = Substring(@hexdigits, @rem + 1, 1) + @hex
END
-- IF @padLength IS NOT NULL
-- IF @padLength > Len(@hex)
-- SET @hex = Right(Replicate('0',@padLength) + @hex, @padLength)
RETURN @hex
END
GO
December 7, 2006 at 6:39 pm
You can use NewID for this:
Select
Cast(Cast(NewID() as nvarchar(255)) As nvarchar(20))
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply