{question}
How can I resolve the following errors when creating a Table-Valued Function (TVF) that utilizes the SPLIT() function with VARCHAR parameters?
- ERROR 2357 UNKNOWN_ERR_CODE: Cannot split string '<text parameter>' (which has type 'BIGINT') with delimiter '<delimiter parameter>' (which has type 'BIGINT')
- ERROR 2357 (HY000): Cannot split string '<text parameter>' (which has type 'BIGINT') with delimiter '<delimiter parameter>' (which has type 'BIGINT').
{question}
{answer}
If you encounter the following error in a TVF that uses the SPLIT() function to split a text string or VARCHAR is passed as a parameter, you can resolve it by explicitly typecasting the parameters to the SPLIT() function, as shown below.
mysql> create function test_split(pin_text varchar(4000), pin_delimiter varchar(1))
-> returns table
-> as
-> return select * from table(SPLIT(pin_text,pin_delimiter));
ERROR 2357 (HY000): Cannot split string 'pin_text' (which has type 'BIGINT') with delimiter 'pin_delimiter' (which has type 'BIGINT').
mysql>
Solution: You need to explicitly typecast the text parameters passed to the SPLIT() function, as demonstrated below:
SPLIT(pin_text :> varchar(4000),pin_delimiter :> varchar(1))
mysql> create function test_split(pin_text varchar(4000), pin_delimiter varchar(1))
-> returns table
-> as
-> return select * from table(SPLIT(pin_text :> varchar(4000),pin_delimiter :> varchar(1)));
Query OK, 0 rows affected (0.01 sec)
The function can be executed as follows:
mysql> select * from test_split('a,c,b,g,d',',');
+-----------+
| table_col |
+-----------+
| a |
| c |
| b |
| g |
| d |
+-----------+
5 rows in set (0.02 sec)
mysql>
mysql> select * from test_split('a:c:b:g:d',':');
+-----------+
| table_col |
+-----------+
| a |
| c |
| b |
| g |
| d |
+-----------+
5 rows in set (0.00 sec)
{answer}