Friday, July 30, 2010

select all the name whose first letter is in caps


SQL> create table t_icap(name varchar2(100));

Table created.

SQL> insert into t_icap values('Srikar');

1 row created.

SQL> insert into t_icap values('Rajj');

1 row created.

SQL> insert into t_icap values('Plsql');

1 row created.

SQL> insert into t_icap values('sqll');

1 row created.

SQL> insert into t_icap values('unix');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t_icap;
Srikar
Rajj
Plsql
sqll
unix

Method 1:
SQL> select * from t_icap where name between 'A' and 'Z';
Srikar
Rajj
Plsql

Method 2:
SQL> select * from t_icap where regexp_like(name,'^[A-Z]');
Srikar
Rajj
Plsql

Method 3:
SQL> select * from t_icap where name=initcap(name);
Srikar
Rajj
Plsql

Method 4:
SQL> select * from t_icap where substr(name,1,1) between 'A' and 'Z';
Srikar
Rajj
Plsql

No comments: