Search a table for a specific value

[Shestakov]

select 1 from dual
where exists (select 1 from emp where name like '%Smith%' )
union
select 0 from dual
where not exists (select 1 from emp where name like '%Smith%' )