Meme Monday: SQL Horoscope: Finding Your Chinese Zodiac

Chinese Zodiac Figures by Joe Ledbetter

It’s time for July’s Meme Monday post, and this month’s topic is SQL Horoscopes. I read Jen McCown’s post, Meme Monday SQLstrology – Table Valued Zodiac Function, and it inspired me to write a simple function for finding the Chinese Zodiac animal for any given year.

There are 12 Chinese Zodiac characters. I took the most recent year for each character and figured out the modulo for each animal. From there, it’s a simple matching of sign modulo value to the modulo of the year being passed in. It’s not a strict interpretation of the Chinese Zodiac as I would have to take into account specific time ranges instead of just the years. Consider this more of an implementation of the version you’d find on a placemat in a Chinese Restaurant in America.

The Function

Create Function dbo.fnChineseZodiac (
    @Year int)
    Returns varchar(12)
As
Begin
    Declare @Animal varchar(12);
    Declare @CZ Table (YearMod int not null primary key,
        Animal varchar(12) not null);
    Insert Into @CZ
    Values (0, 'Monkey'),
        (1, 'Rooster'),
        (2, 'Dog'),
        (3, 'Pig'),
        (4, 'Rat'),
        (5, 'Ox (Bull)'),
        (6, 'Tiger'),
        (7, 'Rabbit'),
        (8, 'Dragon'),
        (9, 'Snake'),
        (10, 'Horse'),
        (11, 'Sheep (Goat)');
    Select @Animal = Animal
    From @CZ
    Where YearMod = @Year%12;
    Return @Animal;
End

Sample Excution

Select dbo.fnChineseZodiac (1970);

————

Dog

(1 row(s) affected)