![]() I accomplish this using dbo.rangeAB which is included at the end of this post. I know I'm a bit late to the game here but I recently came up with a slick solution for this that leverages a tally table (similar to solution above.) The key difference is that my leverages what I call the Virtual Index to sort the results in descending order without a sort operator in the execution plan. Set = Replicate('0', - + SQL Server, you can try something like the sample below: DECLARE int = 321 Either pad or trim the output to match the number of digits specified. Perform the translation from Dec to Bin It will automatically pad the side with 0s, so that it lines up to 8, 16, or whatever number of bits you want. In my daily use, I find it more useful to either get only up to the highest 1 digit, or specify how many digits I'm expecting back. Here's a bit of a change to the accepted answer from Sean, since I found it limiting to only allow a hardcoded number of digits in the output. Produces this result: num bit7 bit6 bit5 bit4 bit3 bit2 bit1 binary_string binary_string_trimmed Union all select 7 union all select 8 union all select 9 union all select 10) as t +cast(cast(t.Number & 1 as bit) as CHAR(1) )įrom (select 1 as Number union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 PATINDEX('%1%', cast(cast(t.Number & 64 as bit) as CHAR(1)) +cast(cast(t.Number & 1 as bit) as CHAR(1)) ,SUBSTRING(cast(cast(t.Number & 64 as bit) as CHAR(1)) to explicitly answer the question, on MSSQL without using REGEXP (which would make it simple) +cast(cast(t.Number & 1 as bit) as CHAR(1)) as binary_string +cast( cast(t.Number & 2 as bit) as CHAR(1)) +cast( cast(t.Number & 4 as bit) as CHAR(1)) +cast( cast(t.Number & 8 as bit) as CHAR(1)) +cast( cast(t.Number & 16 as bit) as CHAR(1)) +cast( cast(t.Number & 32 as bit) as CHAR(1)) , cast(cast(t.Number & 64 as bit) as CHAR(1)) In my case I really wanted to check if bits were on or off (the data is coming from dotnet eNums).Īccordingly here is an example that will give you seperately and together - bit values and binary string (the big union is just a hacky way of producing numbers that will work accross DBs: select t.Number I was a bit amazed that there wasn't a simple solution posted online (that didn't invovled UDFs). ![]() ![]() Actually this is REALLY SIMPLE using plain old SQL. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |