How to extract strings occurring after a certain character in MySQL?

The first thing to say is that storing delimited list of values in text columns is, in many ways, not a good database design. You should basically rework your database structure, or prepare for a potential world of pain.

A quick and dirty solution is to use a numbers table, or an inline suquery, and to cross join it with the table ; REGEXP_SUBSTR() (available in MySQL 8.0), lets you select a given occurence of a particular pattern.

Here is a query that will extract up to 10 values from the column:

SELECT
    REGEXP_SUBSTR(t.val, '[^#]+', 1, numbers.n) name
FROM
    mytable t
    INNER JOIN (
        SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 
        UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 
        UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
) numbers
    ON REGEXP_SUBSTR(t.val, '[^#]+', 1, numbers.n) IS NOT NULL

Regexp [^#]+ means: as many consecutive characters as possible other than #.

Ths demo on DB Fiddle, when given input string '#name#user#user2#laugh#cry', returns:

| name  |
| ----- |
| name  |
| user  |
| user2 |
| laugh |
| cry   |