| Author |
Topic |
|
Kift
Starting Member
14 Posts |
 Posted - 2009-01-16 : 10:50:05
|
| Hello again.Once again I have come across a problem that I'm stuck on, and would very much appreciate your expertise.I have a function in excel that runs as follows:=DEC2BIN(CODE(MID($A$1,3,+$B34,1))),8)which, as I'm sure you can see, selcts part of a string and then uses the CODE function to convert it to a number and then converts that number to Binary.I'm trying to replicate such a code in SQL. I can select the part of the string that I require, but I become stuck when it comes to converting the text to a number. I haven't started on the decimal to binary section as I can't handle the code section, but if any one here could provide any insights and suggestions as to how to replicate these in SQL it would be greatly appreciated.Many thanks.Kift. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-16 : 11:04:54
|
| use CAST() or CONVERT() to convert to number and then to binary |
 |
|
|
Kift
Starting Member
14 Posts |
Posted - 2009-01-16 : 11:16:13
|
| Visakh, thanks the info. I should have got the binary convertion but in a state of panic about how to do the other bit I didn't even think of those options. I'm just a data analyst whose slowly being moved over to SQL and having to learn a lot as I go so all the help every one here has provided has been great.Just to check one thing though, as the string is character based, for example in excel it could pick out t which the code function would convert to 116 and T would be converted to 84, will the functions do the same.Sorry I'm a bit slow on the uptake I am new to all this and still learning. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-16 : 11:19:30
|
| so you want t to converted to 116 and T to 84 ... then what you need is ASCII() in T-SQL |
|
|
|
Kift
Starting Member
14 Posts |
Posted - 2009-01-16 : 11:27:32
|
| Ah. thanks very much. I had a feeling I hadn't explained it too well in the opening thread.Many thanks for your help.I'll scurry off and get this working.Thanks again. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-16 : 11:30:16
|
| Welcome...let me know if you face any more problem |
|
|
|
Kift
Starting Member
14 Posts |
Posted - 2009-01-19 : 09:42:27
|
| As you guessed, it wasn't long until I hit a snag.The query below was wwritten such that it would be possible to return each of the different stages so that it could be followed through. The first ling selects the history filed and displays it, the second selcts the position 1839, the third converts it to its ascii countapart. The problem I have is converting it to binary.I was expecting to see an 8 charcter binary string returned. Instead it returns a 16 charcter string, which looks hexadecimal in nature but I could be wrong. I've done some digging and it seems it returns 2 charcters for every one binary, at least thats what I understand from the help file. Is there anyway I can change the code so that it returns the 8 charcter binary form I'm used in excel rather then the 16 charcter it currently does.Many thanks.Select hist as Hist,substring(hist,1839,1) as Subs,ASCII(substring(hist,1839,1)) as A,CONVERT(binary (8),ASCII(substring(hist,1839,1)),0) as binfrom standard |
|
|
|
Kift
Starting Member
14 Posts |
Posted - 2009-01-19 : 11:17:56
|
| Don't worry guys and girls I managed to find a Free source code which does this as a function.I have attached the link below should anyone be looking for the same thing.Many thanks for everyones help.http://www.intersoftdevelopment.com/IDI-ASPNET/Free-Source-Code.aspx?CodeID=5&Code=SQL+Server+Convert+Decimal+Number+to+Hex+Binary+Any+Base |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
|
Excel CODE function