Converting numbers to characters

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Converting numbers to characters

Author

Topic

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-10-11 : 09:18:07

Greetings

Consider this, if

1 = A
2 = B
3 = C
.
.
.
26 = Z
27 = AA
28 = AB
52 = AZ
53 = BA
54 = BB
.
.
.
when we get to BZ the next one would be CA. When ZZ is reached, we then go to AAA, AAB, AAC etc

How to do this in SQL Server?

------------->>> BREAKING NEWS!!! <<<-------------

Saddam Hussien has weapons of mass destrcution

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-11 : 09:43:23

how bout this...



Declare @i int

Set @i=542145

Select
	rtrim(ltrim(char(c1)+char(c2)+char(c3)+char(c4)+char(c5)+char(c6)+char(c7)))
From
	(
	Select 
		c1=case when @i>=power(26,6) then ((@i/26/26/26/26/26/26)%26)+64 else 32 end,
		c2=case when @i>=power(26,5) then ((@i/26/26/26/26/26)%26)+64 else 32 end,
		c3=case when @i>=power(26,4) then ((@i/26/26/26/26)%26)+64 else 32 end,
		c4=case when @i>=power(26,3) then ((@i/26/26/26)%26)+64 else 32 end,
		c5=case when @i>=power(26,2) then ((@i/26/26)%26)+64 else 32 end,
		c6=case when @i>=power(26,1) then ((@i/26)%26)+64 else 32 end,
		c7=case when @i>=0 then (@i%26)+65 else 32 end
	) A

Corey

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-10-11 : 10:05:24

Superb!!!!!

How did you think of this solution man?!!!!

------------->>> BREAKING NEWS!!! <<<-------------

Saddam Hussien has weapons of mass destrcution

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-11 : 10:06:25

how on earth do you come up with this stuff, man????

Go with the flow & have fun! Else fight the flow

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-10-11 : 10:10:29

Spirit, that's what I'm trying to figure out.... I was getting there (with base 26 and stuff) but never thought of such sweet solution. Simply superb

------------->>> BREAKING NEWS!!! <<<-------------

Saddam Hussien has weapons of mass destrcution

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-11 : 10:17:23

by looking at it i don't even get it... maybe after half an hour to an hour...

pays to be a math major i guess

Go with the flow & have fun! Else fight the flow

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2004-10-11 : 10:49:33

Unfortunately, it's not base 26: there are 26 one character codes, followed by 26^2 two character codes, followed by 26^3 three character codes, etc. Consequently, Corey's doesn't quite work right (it generates codes that contain @ signs, apart from anything else!)
Try using this instead:


Select
	rtrim(ltrim(char(c1)+char(c2)+char(c3)+char(c4)+char(c5)+char(c6)+char(c7)))
From
	(
	Select 
		c1=case when @i>=321272406 then (((@i-321272406)/26/26/26/26/26/26)%26)+65 else 32 end,
		c2=case when @i>=12356630 then (((@i-12356630)/26/26/26/26/26)%26)+65 else 32 end,
		c3=case when @i>=475254 then (((@i-475254)/26/26/26/26)%26)+65 else 32 end,
		c4=case when @i>=18278 then (((@i-18278)/26/26/26)%26)+65 else 32 end,
		c5=case when @i>=702 then (((@i-702)/26/26)%26)+65 else 32 end,
		c6=case when @i>=26 then (((@i-26)/26)%26)+65 else 32 end,
		c7=case when @i>=0 then (@i%26)+65 else 32 end
	) A

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-11 : 11:13:15

damn combinatorics... i knew i should paid more attention to it in school

Go with the flow & have fun! Else fight the flow

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-11 : 11:25:00

It was still kinda wacked out... you need to use this revised version... sorry


Declare @i int

Set @i=4215

Select
	rtrim(ltrim(char(isnull(c1+64,32))+char(isnull(c2+64,32))+char(isnull(c3+64,32))+char(isnull(c4+64,32))+char(isnull(c5+64,32))+char(isnull(c6+64,32))+char(isnull(c7+64,32)))),
	i,
	c1, c2, c3, c4, c5, c6, c7
From
	(
	Select 
		i,
		c1=case when (i-power(26,5)-power(26,4)-power(26,3)-power(26,2)-power(26,1))/power(26,6)<=0 then null else ((i-power(26,6)-power(26,5)-power(26,4)-power(26,3)-power(26,2)-power(26,1))/power(26,6))%26 end,
		c2=case when (i-power(26,4)-power(26,3)-power(26,2)-power(26,1))/power(26,5)<=0 then null else ((i-power(26,5)-power(26,4)-power(26,3)-power(26,2)-power(26,1))/power(26,5))%26 end,
		c3=case when (i-power(26,3)-power(26,2)-power(26,1))/power(26,4)<=0 then null else ((i-power(26,4)-power(26,3)-power(26,2)-power(26,1))/power(26,4))%26 end,
		c4=case when (i-power(26,2)-power(26,1))/power(26,3)<=0 then null else ((i-power(26,3)-power(26,2)-power(26,1))/power(26,3))%26 end,
		c5=case when (i-power(26,1))/power(26,2)<=0 then null else ((i-power(26,2)-power(26,1))/power(26,2))%26 end,
		c6=case when (i)/power(26,1)<=0 then null else ((i-26)/power(26,1))%26 end,
		c7=i%26
	From (Select i = @i) Z
	) A

Corey

VIG
Yak Posting Veteran

86 Posts

Posted - 2004-10-11 : 18:11:15

[code]declare @t table (n int)
declare @p int
set @p=54
insert @t
select 0 a union all select 1 union all select 2 union all select 3 union all select 4 union all
select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all
select 10 union all select 11 union all select 12 union all select 13 union all select 14 union all
select 15 union all select 16 union all select 17 union all select 18 union all select 19 union all
select 20 union all select 21union all select 22 union all select 23 union all select 24 union all
select 25
select 26*26*a.n+26*b.n+c.n+1,
case when (26*26*a.n+26*b.n+c.n)<26*26 then '' else char(ascii('A')+a.n-1) end +
case when (26*26*a.n+26*b.n+c.n)<26 then '' else char(ascii('A')+b.n-1) end +
char(ascii('A')+c.n)
from @t a ,@t b,@t c
--where 26*26*a.n+26*b.n+c.n=@p-1
order by 26*26*a.n+26*b.n+c.n[/code]

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-10-12 : 05:26:05

Thank you all for your contribution!

------------->>> BREAKING NEWS!!! <<<-------------

Saddam Hussien has weapons of mass destrcution

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources