First of all I want to mention that I use declarative set-based approach. So most (if no every) of my answer will be one statement without procedural part(user-defined functions, loops, control flow statements and so on).
With SQL as first language I am 99% sure I cannot win any challenge, but it is not the case.
Suppose we have question like Briefest code to find vowels and print consonants.
1) Input as subquery
SELECT v"Enter string:",string_agg(r,', 'ORDER BY n)"Vowel Count:",TRANSLATE(v,'aeyiou','')"Remaining characters:"
FROM(SELECT text'LoremIpsumDolorSitAmetConsecuteurAdipscingElit!'v)s
,LATERAL(SELECT c||': '||LENGTH(v)-LENGTH(REPLACE(v,c,''))r,n FROM(VALUES('a',1),('e',2),('i',3),('o',4),('u',5))t(c,n))b
GROUP BY v
As you can see the input is subquery. (SELECT text'LoremIpsumDolorSitAmetConsecuteurAdipscingElit!'v)s
.
I count it as empty:
(SELECT text''v)s -- 17 characters
This approach forces me to:
- ☒ Unnecessary subquery
- ☒ Often casting types needed
- ☒ One input per statement(could use
VALUES
orUNION ALL
) - ☑ Fully-contained one query
2) Create structures
-- preparing input
CREATE TABLE s(v text);
INSERT INTO s(v)
VALUES ('LoremIpsumDolorSitAmetConsecuteurAdipscingElit!'),('SQL rulez');
main query:
SELECT v"Enter string:",string_agg(r,', 'ORDER BY n)"Vowel Count:",TRANSLATE(v,'aeyiou','')"Remaining characters:"
FROM s
,LATERAL(SELECT c||': '||LENGTH(v)-LENGTH(REPLACE(v,c,''))r,n FROM(VALUES('a',1),('e',2),('i',3),('o',4),('u',5))t(c,n))b
GROUP BY v
My input is simply FROM s
-- 1 character comparing to 17 before
With this approach:
- ☑ More readable solution
- ☑ I could provide multiple inputs at once
- ☒ Often need to add
GROUP BY
to handle multiple cases - ☒ 3 statmenents instead of one
Should I count preparing input
phase?
3) Input as common-table expression
WITH s(v) AS
( SELECT 'LoremIpsumDolorSitAmetConsecuteurAdipscingElit!'::text
UNION SELECT 'SQL rulez'
UNION SELECT 'Just one more row'
)
SELECT v"Enter string:",string_agg(r,', 'ORDER BY n)"Vowel Count:",TRANSLATE(v,'aeyiou','')"Remaining characters:"
FROM s
,LATERAL(SELECT c||': '||LENGTH(v)-LENGTH(REPLACE(v,c,''))r,n FROM(VALUES('a',1),('e',2),('i',3),('o',4),('u',5))t(c,n))b
GROUP BY v
This approach:
- ☑ One statement solution
- ☑ Clear distinction input-main query
- ☑ Can handle multiple input at once
- ☒ Very long
Could I just count main query?
4) Input as variable
DECLARE @ VARCHAR(100)='My input string'
SELECT LEN(@)
- ☑ Clear indication where input is
- ☑/☒ If not table variable one input only
- ☑ Often does not even need the
FROM
clause and type is known - ☒ Multistatement solution
To sum up:
- Which approach is preferable?
- Should I count input with 2/3/4 cases?
- Handling multiple cases at once will add overhead (grouping/over() clause). Should I take it in consideration and use shorter solution but with one input only (applicable 2/3)?
argv
? If yes to any of those, then there's the answer; this is a paraphrase of Peter Taylor's link. \$\endgroup\$argv
\$\endgroup\$