It could be achieved using SPLIT_TO_TABLE table function:

This table function splits a string (based on a specified delimiter) and flattens the results into rows.

SELECT * 
FROM tab, LATERAL SPLIT_TO_TABLE(column_name, ';')

Looks your data has multiple delimiters , We can leverage STRTOK_SPLIT_TO_TABLE function using multiple delimiters..

STRTOK_SPLIT_TO_TABLE

 WITH data AS (
 SELECT *
    FROM VALUES
           ('Greensboro-High Point-Winston-Salem;Norfolk-Portsmouth-Newport News Washington, D.C. Roanoke-Lynchburg Richmond-Petersburg')
              v( cities))
select * 
    from data, lateral strtok_split_to_table(cities, ';-')
    order by seq, index;

Result:

Your first attempt was very close, you just need to access the out of the flatten, instead of the input to the flatten

so using this CTE for data:

 WITH fake_data AS (
    SELECT *
    FROM VALUES
    ('Greensboro-High Point-Winston-Salem;Norfolk-Portsmouth-Newport News;Washington, D.C.;Roanoke-Lynchburg;Richmond-Petersburg'),
    ('Knoxville'),
    ('Knoxville;Memphis;Nashville')
    v( COL_NAME)
 )

if you had aliased you tables, and accessed the parts.

 SELECT DISTINCT f.value::text as col_name
 FROM fake_data d,
    LATERAL FLATTEN(INPUT=>SPLIT(COL_NAME,';')) f
;

which is what you did in your provided answer, but via SPLIT_TO_TABLE

 SELECT DISTINCT f.value as col_name
 FROM fake_data d,
    TABLE(SPLIT_TO_TABLE(COL_NAME,';')) f
;

STRTOK_SPLIT_TO_TABLE also is the same thing:

 SELECT DISTINCT f.value as col_name
 FROM fake_data d,
    TABLE(strtok_split_to_table(COL_NAME,';')) f
;

Which can also be done via a strtok_to_array and FLATTEN that

 SELECT DISTINCT f.value as col_name
 FROM fake_data d,
    TABLE(FLATTEN(input=>STRTOK_TO_ARRAY(COL_NAME,';'))) f
;