How to enter a single quotation mark in Oracle SQL Output?

SELECT S.IMSI||','||CHR(39)||s.SIM||CHR(39)||','||s.STATUS FROM SRC_ESN1 S

Will create output like

404909090123,'02320E34',A

Summing of absolute values in a range using EXCEL

The sum of absolute values present in column AB1 to AB100.

At cell AB 101 enter command =sum(abs(ab1:ab100))
after entering the values type CTRL+SHIFT+ENTER at last. will execute the function as an array function and the function is changed as {=sum(abs(ab1:ab100))}

Another option to perform the same is
=sumproduct(abs(ab1:ab100))

Nested IF using EXCEL Function

=IF(D2=E2,"SAME",(IF(D2>E2,"TGT","SRC")))

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

Excel equivalent for substr(instr()) function

suppose you need to truncate the leading and trailing characters from within an excel file , mainly to remove the prefixes and sufixes adding by rating engine (telecom) and the filenames attached here for example like
(Huawei.00377819.DAT.20091021185132.cc.conv
and
Huawei1.00215367.DAT.20091020101704.cc.conv
)
Huawei. and Huawei1. are the prefixes to remove and .cc.conv is the sufix that need to remove). Since the string .20091 is repeating allover suppose the list is huge like around 50000 plus entries ,we can use the following function in excel which is equivalent for substr(instr()) in Oracle SQL.

=MID(C2566,SEARCH(".20091",C2566)-12,27)

Sample for CASE in Oracle SQL

select a.MOBILENUMBER,a.PLANCODE,
a.SRC_CUR_MTH_CHRG, a.TGT_CUR_MTH_CHRG, abs(abs(a.SRC_CUR_MTH_CHRG)-abs(a.TGT_CUR_MTH_CHRG)) DIFF,
CASE WHEN
(abs(nvl(a.SRC_CUR_MTH_CHRG,0))>abs(nvl(a.TGT_CUR_MTH_CHRG,0)))
THEN
(round(nvl((a.SRC_CUR_MTH_CHRG-
.TGT_CUR_MTH_CHRG)/nullif(a.SRC_CUR_MTH_CHRG,0)*100,0),2))
ELSE
(round(nvl((a.SRC_CUR_MTH_CHRG-a.TGT_CUR_MTH_CHRG)/nullif(a.TGT_CUR_MTH_CHRG,0)*100,0),2))
END
VAR_CURMTH_CHRG from reconn.br_master_table a where a.MOBILENUMBER='911120093786'

EXCEL function to find percentage difference of data match/mismatch(numeric data only)

=IF(L61>J61,100-ABS(((J61-L61)/ABS(L61)))*100,100-ABS(((J61-L61)/ABS(J61)))*100)