MySQL don’t now an explode or split function. But you can use SUBSTRING_INDEX:

SUBSTRING_INDEX(caet_ast.value, "\n", 1) as street_name, 
SUBSTRING_INDEX(caet_ast.value, "\n", -1) as street_nbr, 

This gets Testweg for street_name and 881 for street_nbr if caet_ast.value was “Testweg\n 881”.

The function is also nestable:

SUBSTRING_INDEX(SUBSTRING_INDEX(path, '/', 2), '/', -1)

This makes 3 if path was 1/3/342/357/363.