Base conversion in SQL

Here’s something silly I threw together that no one will probably use, which makes it perfect for posting here. We ran across software that has a table that has an Id that ALSO has a unique char(4) field that, as far as we can tell, serves no rational purpose. But the values need to be unique and alphanumeric, apparently with no special characters at all. The Id field is a positive integer that starts at 1, so it seemed like a fine opportunity to convert to base 62, using 0-9,A-Z, and a-z as the 62 candidate numerals.

We ended up not doing this, since there was more complicated slower and more difficult to comprehend code available, but I still think this is useful:

ALTER FUNCTION [dbo].[Base62Convert] ( @InputInt BIGINT ) RETURNS CHAR(4)
AS
-- Input: Positive Integer
BEGIN
    DECLARE @Result CHAR(4) = ''

    IF (@InputInt < 0)
        return cast('No negative numbers allowed.' as int);

	IF (@InputInt >= POWER(62,4))
		return cast('Number too big, going home.' as int);

    WHILE (@InputInt > 0)
    BEGIN
        SET @Result = CHAR(@InputInt % 62 + 
		CASE WHEN @InputInt % 62 < 10 THEN 48
			WHEN @InputInt %62 < 36 then 55 
			ELSE 61 END)
		+ @Result
	SET @InputInt = FLOOR(@InputInt/62)
    END

    RETURN RIGHT('0000' + @Result, 4)
END

GO

-- Test:
select dbo.base62Convert(power(62,4)-1), power(62,4)-1

Which will give you nice unique 4-character identifiers up to 14,776,335.

Enjoy!

Posted in Data, Software, Work | Leave a comment

An homage to my 21-year-old e-mail account!

It’s a new year, and I should post more, so here’s a random post about stuff from 20+ years ago!

In 1994, after my first year at college, it became clear that I needed an internet connection at home.  I was disconnected.  Sure, I could talk to my new long distance friends on the PHONE, but this was the 90’s!  My terminal addiction rivaled that of my girlfriend’s Candy Crush addiction now (Update: she’s been in successful recovery for months… she’s quilting now).  IgLou – the “Internet Gateway of Louisville” was the only real gig in town, so I signed up.  I was account number 732 – apparently one of their first 1000 customers – something that confused one of the tech support people in 2004 when they told me they’d never seen a three-digit account number before.  Obviously, I’m still rather proud of that, although I think they may have renumbered the accounts since then, and in a recent chat with the IgLou people they’re probably going to do away with my whole account type sometime soon.

The e-mail address that accompanied that account (lynch@iglou.com) was useless at first.  I just needed the account to connect… something on the other end of the line for my modem to talk to.  My university was on BITNET, and while there were some gateways to connect Internet addresses to BITNET ones, I just stuck with 551147@XAVIER.BITNET.  That’s how my classmates knew me and that’s what was important.

I hadn’t had a solid e-mail address before college… I was reasonably active on the Bulletin Board scene, having a Commodore 128, but I made no effort to maintain a profile or personality.  I threw away usernames and passwords the way monkeys throw poo.

Technically I guess that makes my oldest active e-mail account 21 now. My e-mail address is legally old enough to drink! It is roughly as old as Kory, but of course it has changed dramatically less over time.

It’s mostly unused now. I check it for nostalgia every month or so. It’s useful as an SSH client in a pinch, and I think it still grants me dial-up access, though I truly don’t know when I last owned a modem.

Anyway, just a bit of nostalgia there.

Posted in Personal, Random Musings | Leave a comment

Quickie: Alphabet URLs

Curiously, I’ve been meaning to write this post for a few months, then Google went and created Alphabet and it seems a bit more apropos now.  Let me ‘splain:

Go to the URL bar in your web browser (the thingy that tells you what web address you’re viewing) and type “a”.  It should auto-complete for you.  Mine recommends “amazon.com” which, I imagine, is what it probably recommends for the highest share of US web browsing people (the rest of the world may skew more towards alibaba due to sheer volume, but I’m just guessing).  Clear that out and type “b”.  I get “bestbuy.com”, which is a bit surprising because I really don’t shop there very much, but I do use it for price comparison now and again when recommending family members go look at laptops, so, legit.

You see where I’m going.  Keep going, it’s only 20-something letters, you should be able to get through it quickly; faster if you don’t write it down like I did.  Speaking of which, here’s my list: Continue reading

Posted in Personal, Random Musings, Website | 2 Comments

5-Gallon Bucket Pond Filter

Every once in a while I actually DO one of those little projects on Pinterest. Of all the things I put together, the main reason I thought I’d post this one is that the other million tutorials out there didn’t quite hit the mark for me, so I pulled things I liked from a variety of other posts and made a few more up and here we are.

So I have this pond. The water is green.  For a year or so it filtered well enough with the usual staples — some barley, checking the pH and other levels, and a basic pump, small three-step filter (bio balls and coarse and fine filters in a little filter box), but it was never really clear, just clear enough.  The best it got was immediately after deep cleanings, but they didn’t last more than a month.  For reference, I have a small pond, about 100 gallons, with three 6-10″ long Koi; it tends to be big rains or the direct sun that seems to spawn the evil algae blooms.

Anyway, the design I went for was pretty simple.  First off, here’s the shopping list:

  • 5 Gallon Bucket with lid ($5 at a hardware store)
  • A 10 pound or 0.5 cubic foot bag of lava rock ($4)
  • Some fine filtration media ($9 for plenty)
  • Enough garden hose to reach across the pond and into the bucket (under $10)
  • A cheap hose adapter ($3 for each female and male, or skip if you have an intact hose)
  • Male and female 1″ PVC adapters (less than $1 a piece)
  • Some cheap, sturdy, plastic box that fits at the bottom of the bucket.  I used a large glad-ware container ($3)

Most of these you probably have lying around.  The total cost here is $36, and it’s probably more like $25 if you scrounge some things you have lying around.

Tool wise, all I used was a drill and a pocket knife.

Continue reading

Posted in Hardware, Personal, Random Musings | 4 Comments

When the “Quantified Self” meets Medical Reality

So, there was a bit of a twitter row a few weeks ago when Mark Cuban and a number of healthcare people got in a debate about the frequency of lab testing.  Mark posted a tweet that started, according to Forbes: “A digital firestorm“.  I think the original tweets were deleted, but the Forbes article says they were:

I first heard about this from Aaron Carroll (@aaronecarroll) on The Incidental Economist (TIE) blog.  I’m a regular follower of the blog, and I have a lot of respect for Aaron; his writing is well researched and delivered; he writes for a few sites and it’s all great — here’s his most recent piece on wasting research on settled science, which is spot on, as most of his writing is.  Anyway, the blog (TIE) hits that sweet spot where I agree almost always, but occasionally see a different perspective.  The primary reason for this is that what I do (all things Data) is central to medical research (Dr. Carroll’s main blog focus), but also completely removed from it since I don’t typically use research-level medical data.

I’d pretty much resolved to stay out of this discussion (beyond what I did on twitter), but Aaron posted a video on the topic yesterday, refreshing it in my mind, and this particular topic crosses several personal sweet spots, so I thought I’d chime in.  I think my blog has like 5 readers, so it shouldn’t matter much anyway.

Continue reading

Posted in Data, happytechnologist, Healthcare | 1 Comment