-- MySQL-compatible string function tests
-- LOCATE function tests
SELECT LOCATE('world', 'hello world');

+-------------------------------------------+
| locate(Utf8("world"),Utf8("hello world")) |
+-------------------------------------------+
| 7                                         |
+-------------------------------------------+

SELECT LOCATE('xyz', 'hello world');

+-----------------------------------------+
| locate(Utf8("xyz"),Utf8("hello world")) |
+-----------------------------------------+
| 0                                       |
+-----------------------------------------+

SELECT LOCATE('o', 'hello world');

+---------------------------------------+
| locate(Utf8("o"),Utf8("hello world")) |
+---------------------------------------+
| 5                                     |
+---------------------------------------+

SELECT LOCATE('o', 'hello world', 5);

+------------------------------------------------+
| locate(Utf8("o"),Utf8("hello world"),Int64(5)) |
+------------------------------------------------+
| 5                                              |
+------------------------------------------------+

SELECT LOCATE('o', 'hello world', 6);

+------------------------------------------------+
| locate(Utf8("o"),Utf8("hello world"),Int64(6)) |
+------------------------------------------------+
| 8                                              |
+------------------------------------------------+

SELECT LOCATE('', 'hello');

+--------------------------------+
| locate(Utf8(""),Utf8("hello")) |
+--------------------------------+
| 1                              |
+--------------------------------+

SELECT LOCATE('世', 'hello世界');

+--------------------------------------+
| locate(Utf8("世"),Utf8("hello世界")) |
+--------------------------------------+
| 6                                    |
+--------------------------------------+

SELECT LOCATE(NULL, 'hello');

+----------------------------+
| locate(NULL,Utf8("hello")) |
+----------------------------+
|                            |
+----------------------------+

SELECT LOCATE('o', NULL);

+------------------------+
| locate(Utf8("o"),NULL) |
+------------------------+
|                        |
+------------------------+

-- ELT function tests
SELECT ELT(1, 'a', 'b', 'c');

+---------------------------------------------+
| elt(Int64(1),Utf8("a"),Utf8("b"),Utf8("c")) |
+---------------------------------------------+
| a                                           |
+---------------------------------------------+

SELECT ELT(2, 'a', 'b', 'c');

+---------------------------------------------+
| elt(Int64(2),Utf8("a"),Utf8("b"),Utf8("c")) |
+---------------------------------------------+
| b                                           |
+---------------------------------------------+

SELECT ELT(3, 'a', 'b', 'c');

+---------------------------------------------+
| elt(Int64(3),Utf8("a"),Utf8("b"),Utf8("c")) |
+---------------------------------------------+
| c                                           |
+---------------------------------------------+

SELECT ELT(0, 'a', 'b', 'c');

+---------------------------------------------+
| elt(Int64(0),Utf8("a"),Utf8("b"),Utf8("c")) |
+---------------------------------------------+
|                                             |
+---------------------------------------------+

SELECT ELT(4, 'a', 'b', 'c');

+---------------------------------------------+
| elt(Int64(4),Utf8("a"),Utf8("b"),Utf8("c")) |
+---------------------------------------------+
|                                             |
+---------------------------------------------+

SELECT ELT(NULL, 'a', 'b', 'c');

+-----------------------------------------+
| elt(NULL,Utf8("a"),Utf8("b"),Utf8("c")) |
+-----------------------------------------+
|                                         |
+-----------------------------------------+

-- FIELD function tests
SELECT FIELD('b', 'a', 'b', 'c');

+------------------------------------------------+
| field(Utf8("b"),Utf8("a"),Utf8("b"),Utf8("c")) |
+------------------------------------------------+
| 2                                              |
+------------------------------------------------+

SELECT FIELD('d', 'a', 'b', 'c');

+------------------------------------------------+
| field(Utf8("d"),Utf8("a"),Utf8("b"),Utf8("c")) |
+------------------------------------------------+
| 0                                              |
+------------------------------------------------+

SELECT FIELD('a', 'a', 'b', 'c');

+------------------------------------------------+
| field(Utf8("a"),Utf8("a"),Utf8("b"),Utf8("c")) |
+------------------------------------------------+
| 1                                              |
+------------------------------------------------+

SELECT FIELD('A', 'a', 'b', 'c');

+------------------------------------------------+
| field(Utf8("A"),Utf8("a"),Utf8("b"),Utf8("c")) |
+------------------------------------------------+
| 0                                              |
+------------------------------------------------+

SELECT FIELD(NULL, 'a', 'b', 'c');

+-------------------------------------------+
| field(NULL,Utf8("a"),Utf8("b"),Utf8("c")) |
+-------------------------------------------+
| 0                                         |
+-------------------------------------------+

-- INSERT function tests
SELECT INSERT('Quadratic', 3, 4, 'What');

+----------------------------------------------------------+
| insert(Utf8("Quadratic"),Int64(3),Int64(4),Utf8("What")) |
+----------------------------------------------------------+
| QuWhattic                                                |
+----------------------------------------------------------+

SELECT INSERT('Quadratic', 3, 100, 'What');

+------------------------------------------------------------+
| insert(Utf8("Quadratic"),Int64(3),Int64(100),Utf8("What")) |
+------------------------------------------------------------+
| QuWhat                                                     |
+------------------------------------------------------------+

SELECT INSERT('Quadratic', 0, 4, 'What');

+----------------------------------------------------------+
| insert(Utf8("Quadratic"),Int64(0),Int64(4),Utf8("What")) |
+----------------------------------------------------------+
| Quadratic                                                |
+----------------------------------------------------------+

SELECT INSERT('hello', 1, 0, 'X');

+---------------------------------------------------+
| insert(Utf8("hello"),Int64(1),Int64(0),Utf8("X")) |
+---------------------------------------------------+
| Xhello                                            |
+---------------------------------------------------+

SELECT INSERT('hello世界', 6, 1, 'の');

+--------------------------------------------------------+
| insert(Utf8("hello世界"),Int64(6),Int64(1),Utf8("の")) |
+--------------------------------------------------------+
| helloの界                                              |
+--------------------------------------------------------+

SELECT INSERT(NULL, 1, 1, 'X');

+------------------------------------------+
| insert(NULL,Int64(1),Int64(1),Utf8("X")) |
+------------------------------------------+
|                                          |
+------------------------------------------+

-- SPACE function tests
SELECT SPACE(5);

+-----------------+
| space(Int64(5)) |
+-----------------+
|                 |
+-----------------+

SELECT SPACE(0);

+-----------------+
| space(Int64(0)) |
+-----------------+
|                 |
+-----------------+

SELECT SPACE(-1);

+------------------+
| space(Int64(-1)) |
+------------------+
|                  |
+------------------+

SELECT CONCAT('a', SPACE(3), 'b');

+---------------------------------------------+
| concat(Utf8("a"),space(Int64(3)),Utf8("b")) |
+---------------------------------------------+
| a   b                                       |
+---------------------------------------------+

SELECT SPACE(NULL);

+-------------+
| space(NULL) |
+-------------+
|             |
+-------------+

-- FORMAT function tests
SELECT FORMAT(1234567.891, 2);

+---------------------------------------+
| format(Float64(1234567.891),Int64(2)) |
+---------------------------------------+
| 1,234,567.89                          |
+---------------------------------------+

SELECT FORMAT(1234567.891, 0);

+---------------------------------------+
| format(Float64(1234567.891),Int64(0)) |
+---------------------------------------+
| 1,234,568                             |
+---------------------------------------+

SELECT FORMAT(1234.5, 4);

+----------------------------------+
| format(Float64(1234.5),Int64(4)) |
+----------------------------------+
| 1,234.5000                       |
+----------------------------------+

SELECT FORMAT(-1234567.891, 2);

+----------------------------------------+
| format(Float64(-1234567.891),Int64(2)) |
+----------------------------------------+
| -1,234,567.89                          |
+----------------------------------------+

SELECT FORMAT(0.5, 2);

+-------------------------------+
| format(Float64(0.5),Int64(2)) |
+-------------------------------+
| 0.50                          |
+-------------------------------+

SELECT FORMAT(123, 2);

+-----------------------------+
| format(Int64(123),Int64(2)) |
+-----------------------------+
| 123.00                      |
+-----------------------------+

SELECT FORMAT(NULL, 2);

+-----------------------+
| format(NULL,Int64(2)) |
+-----------------------+
|                       |
+-----------------------+

-- Combined test with table
CREATE TABLE string_test(idx INT, val VARCHAR, ts TIMESTAMP TIME INDEX);

Affected Rows: 0

INSERT INTO string_test VALUES
(1, 'hello world', 1),
(2, 'foo bar baz', 2),
(3, 'hello世界', 3);

Affected Rows: 3

SELECT idx, val, LOCATE('o', val) as loc FROM string_test ORDER BY idx;

+-----+-------------+-----+
| idx | val         | loc |
+-----+-------------+-----+
| 1   | hello world | 5   |
| 2   | foo bar baz | 2   |
| 3   | hello世界   | 5   |
+-----+-------------+-----+

SELECT idx, val, INSERT(val, 1, 5, 'hi') as inserted FROM string_test ORDER BY idx;

+-----+-------------+----------+
| idx | val         | inserted |
+-----+-------------+----------+
| 1   | hello world | hi world |
| 2   | foo bar baz | hiar baz |
| 3   | hello世界   | hi世界   |
+-----+-------------+----------+

DROP TABLE string_test;

Affected Rows: 0