Sql Only String search

Question

with temp(name) as (select 'abc' from dual
union select '123abc' from dual
union select '1abc3' from dual)
select * from temp where temp.name  like '%[a-z]%'

why i am not able to get all the 3 records in output , I am using Oracle SQL Developer 11g

Reason why i am using this query is : I want to check if Desired column Contains only String no Number and Special Character

Answer 1

You are mixing SQL Server and Oracle syntax:

SQL SERVER Demo:

with temp(name) as (
      select 'abc' union all select '123abc'  union select '1abc3' 
)
select * from temp where temp.name like '%[a-z]%';
                                        -- [] is T-SQL specific

ORACLE Demo:

with temp(name) as (select 'abc' from dual
union select '123abc' from dual
union select '1abc3' from dual)
select * from temp where regexp_like(temp.name, '[a-z]')