My Dad gave me a book recently – the 100 Greatest Cricketers. As well as selecting what – in his opinion at least – were the 100 finest exponents of the game, the author also decided to rank them in order from 1 to 100.
At this point, for those who don’t know, I should perhaps explain that Cricket is one of those games given by the English to the rest of the world….to prove what sporting losers the English could be.
For any Australians reading, to whom this statement may ring hollow given their teams current travails, fret not. Historically, England’s brief ascendancy the battle for the Ashes tends to come to a juddering halt when least expected, usually in the form of a 4-0 thrashing ( think 1958-59, 1989).
Anyway, back to the book. Comparing players across different eras is hard enough – just how would you evaluate the relative merits of Sachin Tendulkar and Sir Jack Hobbs, for example ?
But comparing the relative merits of a batsmen and bowler who were contemporaries in the same team is equally problematic. Who is the greater cricketer out of Dennis Lillee and Greg Chappell, or Malcolm Marshall and Viv Richards ?
All of this brings to mind the saying, “how long is a piece of string”.
In SQL, at least, we do have an answer to this question…or do we ?
The LENGTH function
Simple enough really, if you want to know the length of a string, you just need to do something like this (Fred Trueman would approve):
SELECT LENGTH('line') FROM dual;
All as expected. Obviously, if the string you pass in is NULL, then LENGTH will return 0…or will it ?
Time for a cricketing themed example…
CREATE TABLE greats( first_name VARCHAR2(50), last_name VARCHAR2(50), sobriquet VARCHAR2(100)) / INSERT INTO greats( first_name, last_name, sobriquet) VALUES( 'SACHIN', 'TENDULKAR', 'Little Master') / INSERT INTO greats( first_name, last_name, sobriquet) VALUES( 'JACK', 'HOBBS', 'The Master') / INSERT INTO greats( first_name, last_name, sobriquet) VALUES('RAHUL', 'DRAVID', 'The Wall') / INSERT INTO greats( first_name, last_name, sobriquet) VALUES('VICTOR', 'TRUMPER', NULL) / INSERT INTO greats( first_name, last_name, sobriquet) VALUES('DONALD', 'BRADMAN', 'The Don') / INSERT INTO greats( first_name, last_name, sobriquet) VALUES('FREDERICK', 'SPOFFORTH', 'The Demon') / COMMIT;
Not quite sure why Victor Trumper never got his own nickname. By all accounts he was a modest man, but a genius of a cricketer….rather like a lot of the other players in our table. Anyway, this does at least give us the chance to observe what happens when we do this :
SQL> SELECT last_name, LENGTH(sobriquet) 2 FROM greats; LAST_NAME LENGTH(SOBRIQUET) ------------------------------ ------------------ TENDULKAR 13 HOBBS 10 DRAVID 8 TRUMPER BRADMAN 7 SPOFFORTH 9 6 rows selected. SQL>
Unlike the COUNT function, LENGTH is not always guaranteed to return an Integer. If the string passed in is NULL, then it will return NULL.
To avoid being tripped up by this, you may well therefore consider the following to be prudent :
SELECT last_name, NVL(LENGTH(sobriquet),0) FROM greats;
I had almost managed to get all the way through this without mentioning England’s latest demonstration of grace in defeat….but Deb has just walked in and “reminded” me about the rugby last week. The Welsh have never been bashful about winning.