modules/sql-engine/src/integrationTest/sql/group1/function/string/test_replace.test

# name: test/sql/function/string/test_replace.test
# description: REPLACE test
# group: [string]

# test replace on NULLs
query T
select REPLACE('This is the main test string', NULL, 'ALT')
----
NULL

query T
select REPLACE(NULL, 'main', 'ALT')
----
NULL

query T
select REPLACE('This is the main test string', 'main', NULL)
----
NULL

# test replace on scalars
query T
select REPLACE('This is the main test string', 'main', 'ALT')
----
This is the ALT test string

query T
select REPLACE('This is the main test string', 'main', 'larger-main')
----
This is the larger-main test string

query T
select REPLACE('aaaaaaa', 'a', '0123456789')
----
0123456789012345678901234567890123456789012345678901234567890123456789

# test replace on tables
statement ok
CREATE TABLE strings(a VARCHAR, b VARCHAR)

statement ok
INSERT INTO strings VALUES ('Hello', 'World'), ('HuLlD', NULL), ('MotörHead','RÄcks'), ('', NULL)

query T
select REPLACE(a, 'l', '-') FROM strings ORDER BY 1
----
(empty)
He--o
HuL-D
MotörHead

query T
select REPLACE(b, 'Ä', '--') FROM strings ORDER BY a
----
NULL
World
NULL
R--cks

query T
select REPLACE(a, 'H', '') FROM strings WHERE b IS NOT NULL ORDER BY a
----
ello
Motöread

query T
SELECT REPLACE('12341234', '1', '55')
----
5523455234

query T
SELECT REPLACE(NULL, '1', '5')
----
null

query T
SELECT REPLACE('1', NULL, '5')
----
null

query T
SELECT REPLACE('11', '1', NULL)
----
null

query T
SELECT REPLACE('11', '1', '')
----
(empty)

# test incorrect usage of replace
statement error
select REPLACE(1)

statement error
select REPLACE(1, 2)

statement error
select REPLACE(1, 2, 3, 4)