Query that will split first name and last name where total length of first name is greater than 30 and the last name part is null
=================================================================================================================================
[This query smartly (maximum) makes use of the substr() and instr() function inbuilt available within Oracle]
select csm.SUBSCRIBER_CODE_N,csm.FIRST_NAME_V,csm.LAST_NAME_V,length(trim(csm.FIRST_NAME_V)) LEN_F_NAME,length(trim(csm.LAST_NAME_V)) LEN_L_NAME,
substr(csm.FIRST_NAME_V,1,(length(trim(csm.first_name_v))-length(substr(csm.FIRST_NAME_V,instr(csm.FIRST_NAME_V,' ',1,2),length(trim(csm.first_name_v)))))) FNAMEPART_SMART_CONVERT,
substr(csm.FIRST_NAME_V,instr(csm.FIRST_NAME_V,' ',1,2),length(trim(csm.first_name_v))) LNAMEPART_SMART_CONVERT ,
length(substr(csm.FIRST_NAME_V,1,(length(trim(csm.first_name_v))-length(substr(csm.FIRST_NAME_V,instr(csm.FIRST_NAME_V,' ',1,2),length(trim(csm.first_name_v))))))) FNAME_LEN_SMART_CONVERT,
length(substr(csm.FIRST_NAME_V,instr(csm.FIRST_NAME_V,' ',1,2),length(trim(csm.first_name_v)))) LNAME_LEN_SMART_CONVERT
from SERVICE_LEVEL gsm,INVOICE_ACCOUNT cam,PROFILE_LEVEL csm
where gsm.ACCOUNT_CODE_N=cam.ACCOUNT_CODE_N and cam.SUBSCRIBER_CODE_N=csm.SUBSCRIBER_CODE_N
and gsm.CONTRACT_TYPE_V='N' and gsm.STATUS_CODE_V not in ('ER','PR','RE','IA')
and csm.SUBS_CATEGORY_CODE_V<>'T'
and (length(trim(csm.FIRST_NAME_V))>30 or length(trim(csm.LAST_NAME_V))>30)
and length(substr(csm.FIRST_NAME_V,instr(csm.FIRST_NAME_V,' ',1,2),length(trim(csm.first_name_v))))<=30
and length(substr(csm.FIRST_NAME_V,1,(length(trim(csm.first_name_v))-length(substr(csm.FIRST_NAME_V,instr(csm.FIRST_NAME_V,' ',1,2),length(trim(csm.first_name_v)))))))<=30
and length(trim(csm.LAST_NAME_V)) is null
order by 1 asc
No comments:
Post a Comment