Create a Hexadecimal string 1

[derren]

This is going to sound daft, but I need to create a hexadecimal string from the identity column of my table. Essentially, this code will prefix a login name so needs to be short, but allow many values. Best we don't dwell on the details of this, just suffice to say that this needs to happen!

So a urn of 16789 will create a prefix code for all employees of this company of "4195"

So we can have "4195john.smith", and "4F3Cjohn.smith" to represent two users from different companies.

The thing is, I can't seem to mess with the cast/convert functions to create this string. Any ideas? Derren
[Mediocre talent - spread really thin]

 

[SQLBill]

You can't use cast or convert since those only change DATATYPEs (ie. datetime to varchar) and hexidecimal is not a valid datatype in MS SQL Server.

You would have to write a script that would convert the integer to hex. If I can come up with one, I'll post it (provided no one beats me to the answer).

-SQLBill

 

[sunila7]

Hi,

Here is an SP on msdn which gives an Hexadecimal o/p for an input...

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_14_8k6m.asp

or u can look up OLE Automation, return codes in BOL

Does this help?

Sunil

 

[derren]

Thanks sunila7, I have looked and added the Microsoft sp_hexadecimal script and called the procedure from my procedure, but the result is that it is inserting a null value in the table. My money is on my parameters, as my undestanding of SQL is minimal at the moment as I am primarily an ASP and foxpro programmer - but I learn fast.

This is my procedure (for its sins!)

CREATE PROCEDURE register @cname varchar(75) AS
declare @newurn bigint
declare @hexvalue varchar
insert company (cname) values (@cname)

SET  @newurn = @@IDENTITY

execute sp_hexadecimal @newurn=@hexvalue OUTPUT

UPDATE company set chex=@hexvalue where curn=@newurn
GO

Am I right in thinking that my execute statement will send the value of the new identity to the stored procedure which will return the hex code (in text format) into the @hexvalue variable? Am am trying to avoid being a snotty nosed newbie, but I think some hand holding is required.

- SQLbill - thanks for the advice, that is very helpful, I am hope that I will be able to write a script enventually! Derren
[Mediocre talent - spread really thin]

 

[derren]

Isn't the parameter @binvalue in the microsoft sp expecting to receive a binary parameter? Or am I way off the mark? Derren
[Mediocre talent - spread really thin]

 

[sunila7]

Hi,

Try this.....


CREATE PROCEDURE register @cname varchar(75) AS
declare @newurn bigint
declare @hexvalue varchar(255)

insert company (cname) values (@cname)

SET @newurn = @@IDENTITY
execute sp_hexadecimal @newurn,@hexvalue OUTPUT

set @hexvalue = rtrim(ltrim(replace(replace(@hexout,0,' '), 'x',' ')))

UPDATE company set chex=@hexvalue where curn=@newurn
GO


hope it helps......


Sunil

 

[derren]

Thanks Sunil, that is working a treat.

I only have an issue with it converting the first 10 value, it appears to remove the trailing zero from the string. I shall tweak the replace commands later.

So the @hexvalue is getting filled with a string from the stored procedure, but the replace command accepts a number for the search? That's interesting.

Is is just a mistake that you used "hexout" for the name of the variable on the replace line?

Is there a way to test the output from a stored procedure without having to run my web page each time? I am used to an immediate window where I can use commands which are executed straight away in foxpro (and VB). Is there an equivalent in SQL? That way I can keep testing the return value from my procedure until I have it right.

Thanks again Derren
[Mediocre talent - spread really thin]

 

[sunila7]

Hi,

Yeah that was a mistake........Sorry about that.... u should be able to test ur Stored procdure from Query Analyzer.... Change ur SP like this and


CREATE PROCEDURE register @cname varchar(75) AS
declare @newurn bigint
declare @hexvalue varchar(255)

insert company (cname) values (@cname)

SET @newurn = @@IDENTITY
Print @@Identity

execute sp_hexadecimal @newurn,@hexvalue OUTPUT
Print @hexvalue

set @hexvalue = rtrim(ltrim(replace(replace(@hexout,0,' '), 'x',' ')))

Print @hexvalue

UPDATE company set chex=@hexvalue where curn=@newurn

GO

After that u can call that SP like this

Execute Register 'New Company Name'

in Query Analyzer......

It will print the values of Identity, @hexvale before and after replace

Hope it helps...

Sunil

 

[derren]

Brilliant, thanks a lot for that Derren
[Mediocre talent - spread really thin]