Function to cast an integer to a varchar where 1 = A, 26 = Z, ...
-
I have an itentity column that I'd like to convert the value to a varchar using the following logic:
1 becomes 0A
2 becomes 0B
26 becomes 0Z
27 becomes A0
28 becomes AA
52 becomes AZ
53 becomes B0
And so on.
The results will be stored in a varchar field in the same table.
Has anyone done such a conversion function?
Thanks in advance,
John
-
there are a lot of script contributions that do decimal to hex and hex to decimal for example, but non of them have the exact format you are looking for;
take a look at the scripts section and see if you can change your logic to store a formatted hex string (ie '0x0A', or change the logic in some of the examples to do what you want:
Scripts Rating Search Score Convert from hexadecimal to binary -and vice versa 3.25 100 Report DTS Error Code and Description 3.6 87.12 Convert Hex value to Signed 64-bit bigint 2.75 86.74 Generating random numbers in a range, sql2k 4 86.74 Random Populate a Table 0 86.74 ufn_vbintohexstr 4.67 86.74 Convert Hex value to Signed 32-bit int 3.57 86.36 Convert Numeric To Hex 3.5 86.36 Execute DTS package using OA procedures 4.25 85.61 Converting Binary data to a Hex Character String 2.67 84.47 HexToInt 5 84.47 Optimized HexToSMALLINT 5 83.71 HexToINT 3 83.71 HexToSmallInt 4.5 83.71 faster dbo.ufn_vbintohexstr - varbinary to hex 5 83.71 Retrieve column attributes 3 83.33 Database Console Command : DBCC page undocumented 5 83.33 Script to perform http(s) post 4.86 82.95 Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Here is an exemple : ( but 53 becomes AZ ?! )
create function dbo.fn_conv ( @number int )
returns varchar(3)
as
begin
-- next 2 variables could be parameters
declare @charset varchar(255)
declare @len int
set @charset = '0ABCDEFGHIJKLMNOPQRSTUVWXYZ'
set @len = len(@charset)
declare @result varchar(255)
set @result = ''
while @number > 0
begin
set @result = substring(@charset,( @number % @len ) + 1,1) + @result
set @number = (@number - ( @number % @len ) ) / @len
end
if @result = '' set @result = '0'
return @result -- eventually, add leading 0
end
go
select dbo.fn_conv(1) --> A
select dbo.fn_conv(10) --> J
select dbo.fn_conv(27) --> A0
select dbo.fn_conv(52) --> AY
select dbo.fn_conv(53) --> AZ
select dbo.fn_conv(54) --> B0
-
or this one:
ALTER function [dbo].[fn_conv] ( @number int )
returns varchar(255)
as
begin
DECLARE @charset varchar(255)
DECLARE @len int
DECLARE @nbr int
DECLARE @s-2 varchar(255)
declare @result varchar(255)
set @len = 27
set @nbr = 0
SET @s-2 = ''
set @result = ''
while @number >= @len
begin
set @number = @number - 27
set @nbr = @nbr + 1
end
if @nbr > 0
begin
set @s-2 = dbo.fn_Conv (@Nbr)
end
set @number = @number + 64
if @number = 64
begin
set @number = 48
end
set @result = @s-2 + char(@number)
while 1 = 1
begin
if not substring(@result, 1,1) = '0'
begin
break
end
set @result = substring(@result, 2, len(@result) - 1)
end
if len(@result) < 2
begin
set @result = right('00' + @result, 2)
end
return @result
end
-
Hey there John, I think you'll like this...
First, your numbering is a bit off... if 1 is "A", and 26 is "Z", and 52 is "AZ", then 53 MUST be "BA"... There is no zero in this numbering scheme...
Second, you don't need a function... just a simple calculated column... I've added two calculated columns using just a bit of mathematical prestidigitation in the example below just so you can see that it works...
Ready?
--===== If the demonstration table exists, drop it
IF OBJECT_ID('TempDB.dbo.#MyHead') IS NOT NULL
DROP TABLE #MyHead
GO
--===== Create the demonstration table with two calculated columns
CREATE TABLE #MyHead
(
RowNum INT IDENTITY(1,1) PRIMARY KEY,
AlphaRowNum AS +CHAR(((RowNum-1)/CAST(POWER(26.0,7.0) AS BIGINT))%26+65)
+CHAR(((RowNum-1)/CAST(POWER(26.0,6.0) AS BIGINT))%26+65)
+CHAR(((RowNum-1)/CAST(POWER(26.0,5.0) AS BIGINT))%26+65)
+CHAR(((RowNum-1)/CAST(POWER(26.0,4.0) AS BIGINT))%26+65)
+CHAR(((RowNum-1)/CAST(POWER(26.0,3.0) AS BIGINT))%26+65)
+CHAR(((RowNum-1)/CAST(POWER(26.0,2.0) AS BIGINT))%26+65)
+CHAR(((RowNum-1)/CAST(POWER(26.0,1.0) AS BIGINT))%26+65)
+CHAR(((RowNum-1)/CAST(POWER(26.0,0.0) AS BIGINT))%26+65),
SomeValue INT,
AlphaSomeValue AS +CHAR(((SomeValue-1)/CAST(POWER(26.0,7.0) AS BIGINT))%26+65)
+CHAR(((SomeValue-1)/CAST(POWER(26.0,6.0) AS BIGINT))%26+65)
+CHAR(((SomeValue-1)/CAST(POWER(26.0,5.0) AS BIGINT))%26+65)
+CHAR(((SomeValue-1)/CAST(POWER(26.0,4.0) AS BIGINT))%26+65)
+CHAR(((SomeValue-1)/CAST(POWER(26.0,3.0) AS BIGINT))%26+65)
+CHAR(((SomeValue-1)/CAST(POWER(26.0,2.0) AS BIGINT))%26+65)
+CHAR(((SomeValue-1)/CAST(POWER(26.0,1.0) AS BIGINT))%26+65)
+CHAR(((SomeValue-1)/CAST(POWER(26.0,0.0) AS BIGINT))%26+65)
)
--===== Populate the table with 100,000 known numbers (RowNum)
-- and 100,000 random numbers (SomeValue)
INSERT INTO #MyHead (SomeValue)
SELECT TOP 100000
RAND(CAST(NEWID() AS VARBINARY))*2147483647+1
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--===== Now, let's check the calculated columns...
SELECT * FROM #MyHead
It'll handle any positive INT although "0" comes out as the very special "AAAAAAA@"
... Lemme know if that helps...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Surely this is base 27?
1 becomes 0A, 2 becomes 0B, 26 becomes 0Z, 27 becomes A0, 28 becomes AA,... seems pretty clear to me. OK, then there's a slight slip in evaluating AZ and B0, which should be 53 and 54, but 'There is no zero in this numbering scheme?' How do you work that out Jeff?
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Heh... Thought I explained it just fine... Tim, the key for me was the value 1 being A, 26 being Z, and 52 being AZ... I should have said, "I don't think you want zero in this numbering scheme but I could be wrong".
John... what do you actually need? Base 26 (A-Z or Base 27 (0, A-Z)?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
REPLACE(CHAR(64+FLOOR(number/27))+CHAR(64+number-(FLOOR(number/27)*27)),'@','0')
Far away is close at hand in the images of elsewhere.
Anon. -
A slightly different take on David's 2 character solution...
REPLACE(CHAR((Number/27)%27+64)+CHAR(Number%27+64),'@','0')
Just remember that both of those 2 character solutions "lose their mind" at a decimal value of 730... David's starts producing special characters at 730 and, when the number get's large enough, start producing nulls. The solution above starts the sequence over... 729 is ZZ, 730 is 00, 731 is 0A.
The reason I was so adament about using Base 26 (A-Z) instead of Base 27 (0,A-Z) is because a "0" (zero) looks a lot like an "O" (alpha "oh") in some fonts (Courier New, especially).
However, if that's what you really want, you still don't need a loop or a function... just a calculated column. Similar to the previous test I posted, here's the base 27 (0, A-Z) solution... oh... almost forgot... BigInt is only necessary on the POWER(27.0,7.0) line... I'm just being lazy by doing a copy of that line everywhere...
--===== If the demonstration table exists, drop it
IF OBJECT_ID('TempDB.dbo.#MyHead') IS NOT NULL
DROP TABLE #MyHead
GO
--===== Create the demonstration table with two calculated columns
CREATE TABLE #MyHead
(
RowNum INT IDENTITY(0,1) PRIMARY KEY,
AlphaRowNum AS REPLACE(
+CHAR((RowNum/CAST(POWER(27.0,7.0) AS BIGINT))%27+64)
+CHAR((RowNum/CAST(POWER(27.0,6.0) AS BIGINT))%27+64)
+CHAR((RowNum/CAST(POWER(27.0,5.0) AS BIGINT))%27+64)
+CHAR((RowNum/CAST(POWER(27.0,4.0) AS BIGINT))%27+64)
+CHAR((RowNum/CAST(POWER(27.0,3.0) AS BIGINT))%27+64)
+CHAR((RowNum/CAST(POWER(27.0,2.0) AS BIGINT))%27+64)
+CHAR((RowNum/CAST(POWER(27.0,1.0) AS BIGINT))%27+64)
+CHAR((RowNum/CAST(POWER(27.0,0.0) AS BIGINT))%27+64)
,'@',0),
SomeValue INT,
AlphaSomeValue AS REPLACE(
+CHAR((SomeValue/CAST(POWER(27.0,7.0) AS BIGINT))%27+64)
+CHAR((SomeValue/CAST(POWER(27.0,6.0) AS BIGINT))%27+64)
+CHAR((SomeValue/CAST(POWER(27.0,5.0) AS BIGINT))%27+64)
+CHAR((SomeValue/CAST(POWER(27.0,4.0) AS BIGINT))%27+64)
+CHAR((SomeValue/CAST(POWER(27.0,3.0) AS BIGINT))%27+64)
+CHAR((SomeValue/CAST(POWER(27.0,2.0) AS BIGINT))%27+64)
+CHAR((SomeValue/CAST(POWER(27.0,1.0) AS BIGINT))%27+64)
+CHAR((SomeValue/CAST(POWER(27.0,0.0) AS BIGINT))%27+64)
,'@',0)
)
--===== Populate the table with 100,000 known numbers (RowNum)
-- and 100,000 random numbers (SomeValue)
INSERT INTO #MyHead (SomeValue)
SELECT TOP 100000
RAND(CAST(NEWID() AS VARBINARY))*2147483647+1
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--===== Now, let's check the calculated columns...
SELECT * FROM #MyHead
Take a look at the alpha for row numbers 0 and 15... that'll be fun tro troubleshoot if you ever need to
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
One BIG problem.
You state: I have an itentity column that I'd like to convert the value to a varchar
This is from the Books OnLine (BOL):
IDENTITY
Indicates that the new column is an identity column. When a new row is added to the table, Microsoft® SQL Server™ provides a unique, incremental value for the column. . The IDENTITY property can be assigned to tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0) columns. . You must specify both the seed and increment or neither. If neither is specified, the default is (1,1).
So, you cannot make an IDENTITY column VARCHAR. You would have to create a new column, update it based on the IDENTITY column and then drop the identity column or keep both columns. Then how would you update the new column when a new row is entered? Trigger?
-SQLBill
-
You would have to create a new column, update it based on the IDENTITY column and then drop the identity column or keep both columns. Then how would you update the new column when a new row is entered? Add Derived Column to the table
Far away is close at hand in the images of elsewhere.
Anon. -
I had the job of creating alpha-numeric barcodes for my current job. I controlled the data and used an identity column to feed the function below.
It is zero based, but 01...z
CREATE FUNCTION [dbo].[fnc_generateBarcode] (@nbr decimal)
RETURNS varchar(6)
AS
BEGIN
DECLARE @result varchar(6), @loop integer, @working integer
SET @loop = 0
SET @result = ''
WHILE @loop < 6
BEGIN
SET @working = (@nbr / power(36, @loop))%36
set @result = substring('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', @working+1, 1) + @result
SET @nbr = @nbr - (@working * power(36, @loop))
SET @loop = @loop + 1
END
RETURN (@result)
END
-
I think I would keep the indentity column, create a function that takes the value in the identity column and converts it to what you want, and lastly create a computed column that uses that function.
-SQLBill
-
Like I said, keep the identity column and add a calculated column. Takes care of new rows, old rows, etc...
Or, you can do like David suggests... the formula's are simple enough... just use a "derived column". Not sure you need a function but that sure would make it easy to do.--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
That's a cool function... change the 36 to 27 and drop the 1-9 from the string and I think it'll work for the 0, A-Z range...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply