7
\$\begingroup\$

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)?
\$\endgroup\$
5
  • 2
    \$\begingroup\$ See Default for Code Golf: Input/Output methods. \$\endgroup\$ May 7, 2016 at 17:11
  • 1
    \$\begingroup\$ The first approach is definitely invalid - it's hardcoding the input. \$\endgroup\$
    – user45941
    May 7, 2016 at 18:09
  • \$\begingroup\$ Does SQL have A) standard input, B) functions or procedures with arguments of some sort or C) access to argv? If yes to any of those, then there's the answer; this is a paraphrase of Peter Taylor's link. \$\endgroup\$
    – cat
    May 8, 2016 at 0:29
  • \$\begingroup\$ @cat A) Yes, table is basic input B) function and procedures are part of procedural part of SQL, as you see my answer is SQL statement only, wrapping everything with function/procedure is overkill because SQL is set-based. 3) Simple query without external tooling has no access to argv \$\endgroup\$
    – lad2025
    May 8, 2016 at 5:35
  • 1
    \$\begingroup\$ Based on: Default for Code Golf: Input/Output methods it looks like that table is valid input and second approach wins. \$\endgroup\$
    – lad2025
    May 8, 2016 at 6:05

2 Answers 2

3
\$\begingroup\$

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.

\$\endgroup\$
2
  • 1
    \$\begingroup\$ I don't know how or why this would get a downvote, but this is correct. \$\endgroup\$
    – cat
    May 11, 2016 at 10:49
  • \$\begingroup\$ Wraping everything with function does not sound good. Still you have to call it from somewhere SELECT/EXEC/CALL function_call(arg1,arg2). Unless you treat command line program like psql/mysql tools. \$\endgroup\$
    – lad2025
    May 11, 2016 at 15:41
1
\$\begingroup\$

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.

\$\endgroup\$

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .