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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy