habitats-redlist-search-listing

select
    TRIM(REPLACE(REPLACE(scientific_name, '[', ''), ']', '')) as scientific_name,
    habitat_unique_id,
    eunis_habitat_code,
    habitat_category,
        replace(habitat_unique_id, 'REDLIST_', '') as redlist_code,
    CONCAT('/habitats_rl/', habitat_unique_id) as habitat_link
from EUNIS.latest.Habitat_Information
where habitat_category = 'REDLIST'
order by
    CASE
        WHEN habitat_unique_id LIKE 'REDLIST_BAL_'
            THEN 'REDLIST_BAL0' + RIGHT(habitat_unique_id, 1)
        ELSE habitat_unique_id
    END