How to cast result of SUBSTRING_INDEX to varchar?

Question

My csv column in mytable contains the following content as TEXT column.

john;doe;12;john@example.com

I want to create a view, but would prefer the resulting table being varchar columns. But instead, I'm getting TEXT with the following select:

CREATE VIEW my_view AS
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(csv, ';', 2), ';', -1) AS test
FROM mytable;

Is it impossible to convert those fields to varchar or char?

Answer 1

But instead, I'm getting TEXT

Where you have taken this info from?

CREATE TABLE mytable (csv VARCHAR(250))
SELECT 'john;doe;12;john@example.com' AS csv;

CREATE VIEW my_view AS
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(csv, ';', 2), ';', -1) AS test
FROM mytable;

CREATE TABLE test
SELECT * FROM my_view;

SHOW CREATE TABLE test;

Table | Create Table                                                                                                                     
:---- | :------------------------------------------------------------------
test  | CREATE TABLE `test` (
      |   `test` varchar(250) DEFAULT NULL
      | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

db<>fiddle here

---

I forgot to mention that the csv field is of type TEXT

If so explicit convertion needed.

CREATE TABLE mytable (csv TEXT)
SELECT 'john;doe;12;john@example.com' AS csv;

CREATE VIEW my_view AS
SELECT CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(csv, ';', 2), ';', -1), CHAR(255)) AS test
FROM mytable;

CREATE TABLE test
SELECT * FROM my_view;

SHOW CREATE TABLE test;

Table | Create Table                                                                                                                     
:---- | :------------------------------------------------------------------
test  | CREATE TABLE `test` (
      |   `test` varchar(255) DEFAULT NULL
      | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

db<>fiddle here