Have a field 'amsl' of type text, with empy string in it
(CASE WHEN amsl = '' then NULL else (TRIM(REPLACE(amsl,'meters',''))*1) end) as amsl1
Ok this should produce amsl1 field of numeric type, coz of "*1"

Should not then there be Null if source data is '' ? Instead I get 0.

btw, if I do
(CASE WHEN amsl = '' then '' else (TRIM(REPLACE(amsl,'meters',''))*1) end) as amsl1
Then the record is not even inside resultset !