SQL日常使用小方法:

一、去重复,没要求,只要求保单号相同记录的第一条记录,其它的不考虑

select * from (
select
      row_number () over (partition by policyno order by proposalno) rn,t.*
  from ods_cmain_all t   where  t.policyno='012012110106006020124000008')
 where rn=1
 

 

二、使用SQL Loader 导入varchar2类型的,时间数据,使用这个函数来判断本条记录to_date是否正确.

CREATE OR REPLACE FUNCTION FUNC_CHECKDATE(str VARCHAR2) RETURN NUMBER AS
  v_date DATE;
BEGIN
  v_date := to_date(str, 'yyyy-mm-dd hh24:mi:ss');
--v_date := to_date(str,'yyyy-mod-dd hh24:mi:ss'); 
  RETURN 1;  ---正确时间
EXCEPTION
  WHEN OTHERS THEN
    RETURN - 1; ----错误时间
END;

三、 截取aa_bbb_c_dddddd_11111_233344这种类型数据中,前三级数据:

    WITH x AS (
      SELECT 'aaa_vvvvvvv_ccccccc_11_22' NAME FROM dual
      UNION
      SELECT 'a_b_c_11_22' NAME FROM dual
      UNION
      SELECT 'aa_bbb_c_dddddd_11111_233344' NAME FROM dual
      UNION
      SELECT 'AA_BB_CC_DD_11_22' NAME FROM dual
   )
select regexp_substr (name,'([[:alnum:]]+_)([[:alnum:]]+_)([[:alnum:]]+)') from x

结果:

1	AA_BB_CC
2	a_b_c
3	aa_bbb_c
4	aaa_vvvvvvv_ccccccc