How to create a function to convert integer number to base10 or base 2.

[EM1107]

Good day everyone.

I hope that anyone can help me on that one. I would like to know if anyone would have a solution for this. I have been requested to create a function in SQL Server to convert the value of an integer value to a Base 10 or a Base 2 depending on some other value set.

Could any one have a solution for me.

 

[born2program]

Hi, Can you give an example of the input and output you would desire?

 

[drlex]

For conversion of an integer to binary, have a look at the function in the section "converting decimal to binary" on this page:

http://improve.dk/archive/2011/07/11/converting-between-base-2-10-and-16-in-t-sql.aspx

Unless I'm missing something in your set-up, a standard integer will be in base10, so you'd only need to paramaterise the function to leave an integer unchanged between input and output if base10 required.


soi là, soi carré

 

[SQLScholar]

Integers are base10 arent they?

Try this link:

http://dzone.com/snippets/convert-integer-base-10&default=false&zid=159&browser=9&mid=0&refresh=0

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[EM1107]

You have requested an example.

Here is one.

For instance in my database I have an integer number = 153245 and I would like to have the the number converted to the following 3@8T.

I hope that can help.

 

[drlex]

integer number = 153245 and I would like to have the the number converted to the following 3@8T."

'@' and 'T' in a base format? Base256 using ASCII codes? That sure ain't no base10 or base2!

I think you need to explain your logic a little more here.



soi là, soi carré

 

[EM1107]

Well I do not have logic on that one. I tried to get more information on that but I could not get anything. All I was told was that I had to use either use Base 2 or base 10 to get that. I have seen some article and could not find anything that could point me in the right direction.

In the case I am working on at this time the value returned by the integer value is use to generate the name of a file. The integer value is use as a document number and the other value is the document generated for that document number and is stored to the network using that new name generated from the document number.

 

[EM1107]

I have done some more testing. I have tested the value 10 and it returned me this @.

 

[drlex]

Hmmm, on that basis, it looks like it could be a base37 using the sequence 0-9,@,A-Z for 0-36

Yet if so, 153245 = 3@8S, not 3@8T.


soi là, soi carré

 

[born2program]

See if this link helps at all.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=109916

 

[EM1107]

This one did not help.

 

[SkipVought]

Actually it is base 36

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[born2program]

Try this function. It takes 2 parameters - input number and base number. After it is created use the function with 36 as base number, example = select dbo.F_NUMB_TO_BASE(153245,36)

create function dbo.F_NUMB_TO_BASE	(
	@Number		numeric(32,0),
	@Base		int
	)
returns varchar(110)
as

begin


declare @Work_Number	numeric(38,0)
declare @Modulus	int
declare @Digits		varchar(36)
declare @Output_String	varchar(110)


if @Number is null or @Base is null or @Base < 2 or @Base > 36 
	begin
	return null
	end

set @Digits = '0123456789@ABCDEFGHIJKLMNOPQRSTUVWXYZ'

set @Output_String = ''
set @Work_Number = @Number

while 1=1  
	begin

	Set @Modulus = convert(int,abs(@Work_Number-(round(@Work_Number/@Base,0,1)*@Base)))

	set @Output_String = substring(@Digits,@Modulus+1,1) + @Output_String

	set @Work_Number = round(@Work_Number/@Base,0,1)

	if @Work_Number = 0	break
	end -- end while

if @Number < 0 set @Output_String = '-'+@Output_String


return @Output_String

end
go

 

[SkipVought]

There is an anomaly!

The implied definition "0-9,@,A-Z for 0-36" appears to be 37, since the user included @ in the characterset.

However, the math indicates base 36 from the expected result, which would imply that the character set is SHIFTED to be, 0-9,@,A-[highlight]Y[/highlight] for 0-36.

I wrote a VB function to do the conversions and that's how i discovered this anomaloy...

Function BaseCon(num As Variant, basFR As Integer, basTO As Integer)
    Dim i As Integer, num10 As Long, byt As String, numTO, a As Integer
    
    'convert num to base10
    If Not basFR = 10 Then
        Select Case basFR
            Case 2, 8
                For i = 0 To Len(num) - 1
                    num10 = num10 + Mid(num, Len(num) - i, 1) * basFR ^ i
                Next
            Case 16
                For i = 0 To Len(num) - 1
                    byt = Mid(num, Len(num) - i, 1)
                    Select Case byt
                        Case "0" To "9"
                        Case Else
                            byt = Asc(byt) - 55
                    End Select
                    num10 = num10 + CInt(byt) * basFR ^ i
                Next
            Case 36
                For i = 0 To Len(num) - 1
                    byt = Mid(num, Len(num) - i, 1)
                    Select Case byt
                        Case "0" To "9"
                        Case Else
                            byt = Asc(byt) - [highlight]54[/highlight]
                    End Select
                    num10 = num10 + CInt(byt) * basFR ^ i
                Next
            Case Else
                BaseCon = "Base From not defined"
        End Select
    Else
        num10 = num
    End If
    
    numTO = num10
    Do
        a = numTO \ basTO
        numTO = numTO / basTO
        
        a = (numTO - a) * basTO
        
        If a > 9 Then
            BaseCon = Chr(a + [highlight]54[/highlight]) & BaseCon
        Else
            BaseCon = a & BaseCon
        End If
        
        numTO = Int(numTO)
    Loop Until numTO = 0
End Function

[highlight]This value[/highlight] is where the disparity comes, in order to accomodate the @ character.


You can never return a Z in a base 10 to 36!!!
[tt]
35 --> Y
36 --> 10
[/tt]
Don't know if this is significant to EM1107



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[EM1107]

Thanks Guys this is pretty much what I was looking for.

 

[SkipVought]

@EM1107, do you care that Z is not part of the character set?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[born2program]

Skip, I wasn't aware of that anomaly and truthfully I don't know if it would or wouldn't be significant to EM1107. Thanks for pointing it out.

 

[EM1107]

Thanks guys Now that I know I will just remove the Z off the list and that will make it pretty close to what I need to do.

Thanks a lots again everyone.

 

[SkipVought]

If you want the character set to incluse Z, then the BASE becomes 37 and the conversion becomes a LOT different!
[tt]
Bas37 Bas10 Bas36
3@8T -->165975 --> 3J2E
30XR <--153245 <-- 3@8S
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]