Number to Binary Conversion - SQL Server Forums

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 2012 Forums

Transact-SQL (2012)

Number to Binary Conversion

Author

Topic

AnsiSQL92
Starting Member

1 Post

Posted - 2014-08-13 : 07:03:08

Hi All,

I need to convert a number to binary format (1 and 0 strings). Is it possible in Standard SQL 92 syntax?

I have a number to binary conversion formula which is valid for Oracle database but I need it's equivalent in Standard SQL 92 syntax.

select
DECODE(BITAND(16, 16), 16, '1', '0') ||
DECODE(BITAND(16, 8), 8, '1', '0') ||
DECODE(BITAND(16, 4), 4, '1', '0') ||
DECODE(BITAND(16, 2), 2, '1', '0') ||
DECODE(BITAND(16, 1), 1, '1', '0') FROM dual;

Please help.

It's urgent.

Thanks Much
Purnima

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-13 : 07:41:56

See here: https://stackoverflow.com/questions/127116/sql-server-convert-integer-to-binary-string

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-13 : 09:05:59

[code]DECLARE @Sample INT = 5,
@Short BIT = 1;

SELECT REPLACE(CASE WHEN @Short = 1 THEN LTRIM(v) ELSE v END, ' ', '0')
FROM (
VALUES (
CASE WHEN @Sample & CAST(2147483648 AS BIGINT) >= 1 THEN '1' ELSE ' ' END +
CASE WHEN @Sample & 1073741824 >= 1 THEN '1' ELSE ' ' END +
CASE WHEN @Sample & 536870912 >= 1 THEN '1' ELSE ' ' END +
CASE WHEN @Sample & 268435456 >= 1 THEN '1' ELSE ' ' END +
CASE WHEN @Sample & 134217728 >= 1 THEN '1' ELSE ' ' END +
CASE WHEN @Sample & 67108864 >= 1 THEN '1' ELSE ' ' END +
CASE WHEN @Sample & 33554432 >= 1 THEN '1' ELSE ' ' END +
CASE WHEN @Sample & 16777216 >= 1 THEN '1' ELSE ' ' END +
CASE WHEN @Sample & 8388608 >= 1 THEN '1' ELSE ' ' END +
CASE WHEN @Sample & 4194304 >= 1 THEN '1' ELSE ' ' END +
CASE WHEN @Sample & 2097152 >= 1 THEN '1' ELSE ' ' END +
CASE WHEN @Sample & 1048576 >= 1 THEN '1' ELSE ' ' END +
CASE WHEN @Sample & 524288 >= 1 THEN '1' ELSE ' ' END +
CASE WHEN @Sample & 262144 >= 1 THEN '1' ELSE ' ' END +
CASE WHEN @Sample & 131072 >= 1 THEN '1' ELSE ' ' END +
CASE WHEN @Sample & 65536 >= 1 THEN '1' ELSE ' ' END +
CASE WHEN @Sample & 32768 >= 1 THEN '1' ELSE ' ' END +
CASE WHEN @Sample & 16384 >= 1 THEN '1' ELSE ' ' END +
CASE WHEN @Sample & 8192 >= 1 THEN '1' ELSE ' ' END +
CASE WHEN @Sample & 4096 >= 1 THEN '1' ELSE ' ' END +
CASE WHEN @Sample & 2048 >= 1 THEN '1' ELSE ' ' END +
CASE WHEN @Sample & 1024 >= 1 THEN '1' ELSE ' ' END +
CASE WHEN @Sample & 512 >= 1 THEN '1' ELSE ' ' END +
CASE WHEN @Sample & 256 >= 1 THEN '1' ELSE ' ' END +
CASE WHEN @Sample & 128 >= 1 THEN '1' ELSE ' ' END +
CASE WHEN @Sample & 64 >= 1 THEN '1' ELSE ' ' END +
CASE WHEN @Sample & 32 >= 1 THEN '1' ELSE ' ' END +
CASE WHEN @Sample & 16 >= 1 THEN '1' ELSE ' ' END +
CASE WHEN @Sample & 8 >= 1 THEN '1' ELSE ' ' END +
CASE WHEN @Sample & 4 >= 1 THEN '1' ELSE ' ' END +
CASE WHEN @Sample & 2 >= 1 THEN '1' ELSE ' ' END +
CASE WHEN @Sample & 1 >= 1 THEN '1' ELSE ' ' END
)
) AS d(v);[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources