Indexing and slicing with brackets

[vanillajonathan]

Python lets you do string slicing using an indexer with bracket syntax like this:

In [1]: hamburger = "hamburger"

In [2]: hamburger[3]
Out[2]: 'b'

In [3]: hamburger[3:]
Out[3]: 'burger'

In [4]: hamburger[3:6]
Out[4]: 'bur'

In [5]: hamburger[:6]
Out[5]: 'hambur'

SQL provides the SUBSTRING() function so PRQL could impement a simliar indexer using brackets as in Python and translate that into SQL.

Docs: https://docs.python.org/3/tutorial/introduction.html#strings

• substring() in IBM Db2
• substring() in MariaDB
• substring() In MySQL
• substring() in Oracle Database
• substring() in PostgreSQL
• substring() in SQLite
• substring() in Microsoft SQL Server

[max-sixty]

Yes I agree this would be good.

I'm not sure what the best syntax for this is. If we want to use something like an indexer with [2:5], then we have to know the type — whether we're indexing a string or an array, for example.

So we could start this with a substring function, and then later promote that to some indexing syntax when we're more confident on how that should work with types.

[eitsupi]

I think [] is confusing because it is already used as a list.

prql/book/src/syntax.md

Line 18 in a82429d

| `[]` | [Lists](./syntax.md#lists) | `select [id, amount]` |

[vanillajonathan]

PHP and JavaScript also uses [] notation for lists but also use it for arrays. (PHP: reference, JS: reference)

[eitsupi]

Currently from a | select [b] cannot be written as from a | select[b].
Doesn't it confuse people that the two uses have different about space?

[vanillajonathan]

I don't know. Maybe it does, because in PHP and JavaScript there is a equal sign when assigning an array, and no word before when passing an array into a function.

let fruits = ["apple", "banana", "pear", "orange"];
let fruit = fruits[1];
eat_fruits(["apple", "banana", "pear", "orange"]);

[vanillajonathan]

#3913 adds support for the substring function.