How should I count bytes with SQL?

Question

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

SqlFiddleDemo

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 or UNION 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

SqlFiddleDemo

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

SqlFiddleDemo

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)?

Answer 1

I think you've misunderstood the previous discussion on default I/O. "Standard input" aka stdin is a byte stream which is one of the standard ways to pass input to a command-line program. (The other way is argv). Unless a question makes special exceptions, or unless you find a way of running an SQL script from the command line and reading its raw input, the only option which is compatible with the defaults is to write a user function which takes inputs as arguments.

Answer 2

I've been following your option #2, which is allowed specifically as an Input method for SQL:

SQLs may take input from a named table

I normally don't even write out the explicit CREATE TABLE and INSERT statements, I just use the table in my code, then mention something like:

Input is taken from integer columns L and x in preexisting table t, per our allowed input methods.

So far I haven't had anyone complain, although SQL answers are seldom competitive by byte count.