Friday, December 11, 2009

Smart use of Substr and Instr function to split first name and last name of subscriber

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: