how to sort data in oracle report order by name.'Mr' is present before the name. example Mr. John Mr. Smith
Plz help me :eek:
your post is a little unclear. 'Mr' or 'Mr. '? If it is present always, order by name itself - it does not have effect on ordering. If only some names contain it, use REGEXP_REPLACE to replace it selectively.
SQL> with tbl_name as (select 'Mr. John' nam from dual union all
2 select 'Mr. Smith' from dual union all
3 select 'Peters' nam from dual )
4 select nam, regexp_replace( nam, '^Mr. ' ) nam_mod
5 from tbl_name
6 order by regexp_replace( nam, '^Mr. ' );
Mr. John John
Mr. Smith Smith
3 rows selected.