T SQL functions

0.cast varchar to int :

SELECT CAST(CAST('7082.7758172' as float) as int)

1.Left Padding with '0', total length is 8

REPLICATE('0', (8 - LEN(EXCHANGERATE))) + EXCHANGERATE

2.Right Padding, total length is 8

 EXCHANGERATE +REPLICATE('0', (8 - LEN(EXCHANGERATE)))

3.Case when

(CASE WHEN  LEN(EXCHANGERATE) =0 
            THEN     '000000000000000000'
             ELSE     substring(RRRID,0,19) 
             END
)

AS  exchangeRate, 

4.CAST   (CALL.TILLID is int, convert to VARCHAR)

SELECT CAST(CALL.TILLID AS  VARCHAR ) TILLID

FROM ABC

5.POWER

SELECT 

(CASE WHEN  b.FOREIGNMINORUNIT>= 2
             THEN ROUND((b.FOREIGNAMOUNT/POWER(10,b.FOREIGNMINORUNIT-2)),0) 
      ELSE ROUND((b.FOREIGNAMOUNT* POWER(10,2-b.FOREIGNMINORUNIT)),0) 
      END )
AS FOREIGNAMOUNT 

FROM ABC b

6.Sample :

select  /*
localMID ,
ForeignMID ,
localTID ,
foreignTID,
TillID,
INVOICENUMBER,
SETTLEMENTBATCHNUMBER,
FCCBatchNo,
AcquirerID,
--card1,
cardNumber,
TRANSACTIONDATE,
FCCPROCESSSTATUS,
baseCurrency,
baseAmount,
foreignCurrency,
foreignAmount,
exchangeRate,
AUTHORIZATIONMESSAGETYPE,
AUTHORIZATIONCODE,
CARDTYPE,
CLEARINGMODE,
(REPLICATE('0', (7 - LEN(cast(seqNo as varchar)))) + cast(seqNo as varchar) ) ,
Reserved,
refID
*/
localMID +
ForeignMID +
localTID +
foreignTID+
TillID+
INVOICENUMBER+
SETTLEMENTBATCHNUMBER+
FCCBatchNo+
AcquirerID+
--card1,
cardNumber+
TRANSACTIONDATE+
FCCPROCESSSTATUS+
baseCurrency+
baseAmount+
foreignCurrency+
foreignAmount+
exchangeRate+
AUTHORIZATIONMESSAGETYPE+
AUTHORIZATIONCODE+
CARDTYPE+
CLEARINGMODE+
(REPLICATE('0', (7 - LEN(cast(seqNo as varchar)))) + cast(seqNo as varchar) ) +
Reserved+
refID


from (
SELECT  rank() OVER (ORDER BY callid asc) as seqNo,
--callid,
REPLICATE(' ', (15 - LEN(MERCHANTID))) + MERCHANTID localMID,
REPLICATE(' ', (15 - LEN(FOREIGNCURRENCYMERCHANTID))) +FOREIGNCURRENCYMERCHANTID ForeignMID,
REPLICATE(' ', (8 - LEN(TERMINALID))) + TERMINALID localTID,


(case  when LEN(FOREIGNCURRENCYTERMINALID) <=8 
       then  REPLICATE(' ', (8 - LEN(FOREIGNCURRENCYTERMINALID))) + FOREIGNCURRENCYTERMINALID
       else substring(FOREIGNCURRENCYTERMINALID,0,9) 
       END
)AS  foreignTID ,


REPLICATE('0', (6 - LEN(TILLID))) + TILLID TillID,
'0000000000' INVOICENUMBER,
'000000' SETTLEMENTBATCHNUMBER,
'000000' FCCBatchNo,
'1501' AcquirerID,
CARDNUMBER CARD1,
--CARDNUMBER +REPLICATE('0', (20 - LEN(CARDNUMBER))) cardNumber,
(case when len(CardNumber)<=20
     then
CARDNUMBER + REPLICATE('0', (20 - LEN(CARDNUMBER)))
else substring(cardNumber,0,21)
END) AS  CARDNUMBER,


TRANSACTIONDATE,
FCCPROCESSSTATUS,
REPLICATE('0', (4 - LEN(BASENUMISOCODE))) + BASENUMISOCODE baseCurrency,
REPLICATE('0', (12 - LEN(BASEAMOUNT))) + BASEAMOUNT baseAmount,
(case when len(foreignamount)=0 then '0000'
      else REPLICATE('0', (4 - LEN(FOREIGNNUMISOCODE))) + FOREIGNNUMISOCODE 
 END)  AS foreignCurrency,


(case when len(cast(foreignamount as varchar))=0 then '000000000000'
      else REPLICATE('0', (12 - LEN(cast(foreignamount as varchar)))) + cast(foreignamount as varchar) 
 END)  AS foreignAmount,


-- EXCHANGERATE,
(case  when LEN(EXCHANGERATE) <8 
       then  REPLICATE('0', (8 - LEN(EXCHANGERATE))) + EXCHANGERATE
       else substring(EXCHANGERATE,0,9) 
       END
)AS  exchangeRate, 


/*
(case  when LEN(EXCHANGERATE) <8 
       then  REPLICATE('0', (8 - LEN(EXCHANGERATE))) + EXCHANGERATE
       else substring(EXCHANGERATE,0,9) 
       END
)AS  exchangeRate2,  */


AUTHORIZATIONMESSAGETYPE,
AUTHORIZATIONCODE,
REPLICATE('0', (3 - LEN(CARDTYPE))) + CARDTYPE CARDTYPE,
CLEARINGMODE,
--'0000000' RcNumber,  -- 7 
'0' Reserved,


(case  when LEN(EXCHANGERATE) =0 
       then  '000000000000000000'
       else substring(RRRID,0,19) 
       END
)AS  refID


From  
(
select --top 10 
call.callid,
CALL.MERCHANTID,
CALL.TERMINALID,
--CALL.TILLID,
CAST(CALL.TILLID AS varchar) TILLID,
b.FOREIGNCURRENCYMERCHANTID,
b.FOREIGNCURRENCYTERMINALID,
substring(b.FOREIGNCURRENCYTERMINALID,0,9) FOREIGNCURRENCYTERMINALID_G ,
'0000000000' INVOICENUMBER,
'000000' SETTLEMENTBATCHNUMBER,


(CASE WHEN b.CARDNUMBER IS NULL
     THEN '0000'
ELSE '0000'+b.CARDNUMBER
end) as CARDNUMBER,


b.CARDNUMBER CARDNUMBERoRI,
b.localtransactiondate RATERETRIEVALDATE,
substring(RRRID,19,14) TRANSACTIONDATE, 
'00' FCCPROCESSSTATUS,
BASENUMISOCODE BASENUMISOCODE1,  
(case when b.BASENUMISOCODE is null
     then '0000'
else CAST(b.BASENUMISOCODE  AS varchar) 
end 
) As BASENUMISOCODE,
CAST(b.BASEAMOUNT  AS varchar)  BASEAMOUNT,
--b.BASEMINORUNIT,--FIN1 Version 1.4 
FOREIGNNUMISOCODE FOREIGNNUMISOCODE1,
(case when b.FOREIGNNUMISOCODE is null
     then '0000'
else CAST(b.FOREIGNNUMISOCODE  AS varchar) 
end )AS FOREIGNNUMISOCODE,
--CAST(b.FOREIGNAMOUNT  AS varchar)  FOREIGNAMOUNT,
b.FOREIGNAMOUNT  FOREIGNAMOUNT_ORI,
b.FOREIGNMINORUNIT,
(case when FOREIGNAMOUNT is null
     then '0' 
else 
    (case when b.FOREIGNMINORUNIT>= 2
     then ROUND((b.FOREIGNAMOUNT/POWER(10,b.FOREIGNMINORUNIT-2)),0) 
else ROUND((b.FOREIGNAMOUNT* POWER(10,2-b.FOREIGNMINORUNIT)),0) 
end )
END)
AS FOREIGNAMOUNT ,
EXCHANGERATE EXCHANGERATE1,
(case when EXCHANGERATE is null 
      then '0'
 else CAST(b.EXCHANGERATE  AS varchar) 
END )
AS EXCHANGERATE,
'00' AUTHORIZATIONMESSAGETYPE,
'         'AUTHORIZATIONCODE,
(case when b.CARDTYPE is null
    then '000'
else b.CARDTYPE
end ) AS cardType,
'0' CLEARINGMODE,
b.RRRID


from  call call, fccservicecall  b
where localtransactiondate >'2014-06-20 00:00:00.000'  
and  
localtransactiondate <'2014-06-22 00:00:00.000'
and call.callid = b.callid
and actioncode='8800' and errormessage is null
--and call.acquirerid=1501
--and b.basenumisocode=978
--order by CALL.callid desc
)aa -- order by callid asc
)bb