SQL Convert Number to String Representation of Any Base (Binary, Hexadecimal, ..., Tricontahexadecimal)

Convert base 16 strings to base 2 strings in tsql

Keeping in mind my initial goal:

The whole purpose is to find values with at least 4 1s in a row in their binary representation.

I took another route (without suggested functions) and could come up with this solution - first, I used recursion via Common Table Expressions to get the binary representation of needed values like this:

with cte as(
    select 1 code, 29/2 n, 29 - 2*(29/2) bit_val
    union all
    select code, n/2, n - 2*(n/2) from cte
    where n > 0
)
select  code, row_number() over(partition by code order by n desc) 
bit_no, bit_val from cte

which gave me the binary representation of 29 (for example):

-- code is an id of entity where the value I am trying to convert into binary belongs to
---------------------------
| code | bit_no | bit_val |
---------------------------
|  12  |   1    |    1    |
|  12  |   2    |    0    | 
|  12  |   3    |    1    |
|  12  |   4    |    1    |
|  12  |   5    |    1    |
---------------------------

Then I just used an aggregate function (SUM) within a 4-rows window:

select *, sum(bit_val) over(partition by code order by bit_no 
rows between current row and 3 following) bits_in_a_row from cte1

-------------------------------------------
| code | bit_no | bit_val | bits_in_a_row |
-------------------------------------------
|  12  |   1    |    1    |       3       |
|  12  |   2    |    0    |       3       | 
|  12  |   3    |    1    |       3       |
|  12  |   4    |    1    |       2       |
|  12  |   5    |    1    |       1       |
-------------------------------------------

If the sum of cells in 4 consecutive rows is 4 - I got what I was looking for although it is not quite a conversion of base 16 strings to base 2 strings as I initially put it in the caption. The final answer can be achieved taking only identities where bits_in_a_row is equal to 4 (or whatever else).

SQL string literal hexadecimal key to binary and back

In Contradiction to my previous statement, it seems that MySQL's UNHEX() function does a conversion from a string block by block and then concat much like I stated above, so the method works also for HEX literal values which are bigger than the BIGINT's 8 byte limitation. Here an example table that illustrates this:

CREATE TABLE `testdb`.`tab` (
`hexcol_binary` BINARY(16) GENERATED ALWAYS AS (UNHEX(charcol)) STORED,
`charcol` CHAR(32) NOT NULL,
PRIMARY KEY (`hexcol_binary`));

The primary key is a generated column, so that that updates to charcol are the designated way of interacting with the table with string literals from the outside:

REPLACE into tab (charcol) VALUES ('1010202030304040A0A0B0B0C0C0D0D0');
SELECT HEX(hexcol_binary) as HEXstring, tab.* FROM tab;

as seen building keys and indexes on the hexcol_binary works as intended.

To verify the speedup, take

ALTER TABLE `testdb`.`tab` 
ADD INDEX `charkey` (`charcol` ASC);
EXPLAIN SELECT * from tab where  hexcol_binary = UNHEX('1010202030304040A0A0B0B0C0C0D0D0') #keylength 16
EXPLAIN SELECT * from tab where charcol = '1010202030304040A0A0B0B0C0C0D0D0' #keylength 97

the lookup on the hexcol_binary column is much better performing, especially if its additonally made unique.

Note: the hex conversion does not care if the hex-characters A through F are capitalized or not for the conversion process, however the charcol will be very sensitive to this.

I need to be able to generate non-repetitive 8 character random alphanumeric for 2.5 million records

Here's a way that uses a temporary table to assure the uniqueness

Create and fill a #temporary table with unique random 8 character codes.

The SQL below uses a FOR XML trick to generate the codes in BASE62 : [A-Za-z0-9]

Examples : 8Phs7ZYl, ugCKtPqT, U9soG39q

A GUID only uses the characters [0-9A-F].

For 8 characters that can generate 16^8 = 4294967296 combinations.

While with BASE62 there are 62^8 = 2.183401056e014 combinations.

So the odds that a duplicate is generated are significantly lower with BASE62.

The temp table should have an equal of larger amount of records than the destination table.

This example only generates 100000 codes. But you get the idea.

IF OBJECT_ID('tempdb..#tmpRandoms') IS NOT NULL DROP TABLE #tmpRandoms;
CREATE TABLE #tmpRandoms (
    ID INT PRIMARY KEY IDENTITY(1,1), 
    [UniqueID] varchar(8),
    CONSTRAINT UC_tmpRandoms_UniqueID UNIQUE ([UniqueID])
);

WITH DIGITS AS
(  
    select n 
    from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) v(n)
),
NUMS AS
(
    select (d5.n*10000 + d4.n*1000 + d3.n*100 + d2.n * 10 + d1.n) as n
    from DIGITS d1
    cross join DIGITS d2
    cross join DIGITS d3
    cross join DIGITS d4
    cross join DIGITS d5
)
INSERT INTO #tmpRandoms ([UniqueID])
SELECT DISTINCT LEFT(REPLACE(REPLACE((select CAST(NEWID() as varbinary(16)), n FOR XML PATH(''), BINARY BASE64),'+',''),'/',''), 8) AS [UniqueID]
FROM NUMS;

Then update your table with it

WITH CTE AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY ID) AS RN, [UniqueID]
    FROM YourTable
)
UPDATE t 
SET t.[UniqueID] = tmp.[UniqueID]
FROM CTE t
JOIN #tmpRandoms tmp ON tmp.ID = t.RN;

A test on rextester here

Is there a simple way to directly define binary numbers? (such as an equivalent to the 0x-notation for hexadecimal numbers)

In Java 7, there are binary literals. That said, in many higher level applications working with binary directly is difficult to read and often unnecessary.

Using Rhino Mocks, why does invoking a mocked on a property during test initialization return Expected call #1, Actual call #0?

Why in the world would you want to test the same thing each time a test is run?

If you want to test that a specific thing happens, you should check that in a single test.

The pattern you are using now implies that you need to
- set up prerequisites for testing
- do behavior
- check that behavior is correct
and then repeat that several times in one test

You need to start testing one thing for each test, and that help make the tests clearer, and make it easier to use the AAA syntax.

There's several things to discuss here, but it certainly would be clearer if you did it something like:

[TestMethod]
ShouldCallInvokedMethodWhenSettingProperty()
{
   var viewMock = MockRepository.GenerateMock<IView>()

   var presenter = new Presenter(viewMock);

   presenter.Property = 1;

   viewMock.AssertWasCalled(view => view.InvokedMethod());

}

Read up more on Rhino Mocks 3.5 syntax here: http://ayende.com/Wiki/Rhino+Mocks+3.5.ashx

---

Related Topics