With the introduction of the varchar(max) and nvarchar(max) data types, it would seem that one would be safe to use T-SQL and T-SQL functions for large string manipulation. Hold tight though, there are a some instances where this seems to break. Take the replace function for example. Run the code below:
<
You will get the following error:
I'll save you a little time and explain what I see happenning. Replace works with varchar or nvarchar max types for the first parameter, however, it appears the second parameter, or search function, does not accept anything greater than nvarchar(4000) or varchar(8000). If you simply change the replicate on @str1 to replicate 8000 or less times , then you do not even have to recast the varchar(max) to varchar(8000) and it will work.
Interesting behavior and just a little food for thought when consider your large text data types.
Tags: t-sql
Categories: SQL Programming
I found an old USB memory key of mine in an old work bag and plugged it up to see what lost treasures might be on it -- it's a small purvue into old thoughts and coding patterns. One of the interesting things I found among the different code snippets was a mortgage calculator I had written a few years back when my wife and I had bought our first house. It prints your whole schedule, principal and interest and shows the current value of balance at the specific points in time. I never expanded it beyond the typical fixed period, so now that I found it again, I'll probably look at making it more dynamic in so far as allowing you to do schedules for ARM and expected rate increases/decreases.
There are a couple of values to set in order to get your schedule printed out:
-
Quoted Interest Rate
-
Compounding Period your bank uses, most will use months, so this value would be 12
-
Loan Amount
-
Amoritization Period -- this needs to be in the same format as the compounding period, which is months
-
Mortgage Start Date - -when you first start paying the loan
Here's the code, have fun:
/*** Loan Calculation Variables ***/
@InterestRate FLOAT, @Loan FLOAT, @AmoritizationMonths FLOAT, @Payment FLOAT, @Period FLOAT,
/*** CALCULATED PROCEDURAL VARIABLES ***/
@Payment2 FLOAT, @TotalPayment FLOAT, @FinanceCharges FLOAT, @CompoundingPeriod FLOAT,
@CompoundingInterest FLOAT,
/*** CALCULATED LOAN VARIABLES ***/
@CurrentBalance FLOAT,
@Principal FLOAT,
@Interest FLOAT,
/*** MORTGAGE TIME VARIABLES ***/
@MortgageStartDate SMALLDATETIME, @MortgageEndDate SMALLDATETIME,
@MortgagePayDate SMALLDATETIME, @MortgageDueDate SMALLDATETIME
/*** USER VARIABLES ***/
SET @InterestRate = 0.0719
SET @CompoundingPeriod = 12
SET @Loan = 100000
SET @AmoritizationMonths = 360
SET @MortgageStartDate = '2005-2-01'
/*** END USER VARIABLES ***/
SET @CompoundingInterest = @InterestRate/@CompoundingPeriod
SET @Payment =
ROUND(
(
((@InterestRate/12) * @Loan)/(1- ( POWER( (1 + (@InterestRate/12)),(-1 * @AmoritizationMonths) )))
),2)
SET @TotalPayment = @Payment * @AmoritizationMonths
SET @FinanceCharges = @TotalPayment - @Loan
if exists(select object_id from tempdb.sys.objects where name like '#Schedule%')
begin
drop table #SCHEDULE
end
create table #SCHEDULE
(
PERIOD INT
,PAYDATE SMALLDATETIME
,PAYMENT MONEY
,CURRENT_BALANCE MONEY
,INTEREST MONEY
,PRINCIPAL MONEY
)
SET @Period = 1
SET @MortgageEndDate = DATEADD(year,@AmoritizationMonths/12,@MortgageStartDate)
SET @MortgagePayDate = @MortgageStartDate
BEGIN
WHILE (@Period < = @AmoritizationMonths)
BEGIN
SET @CurrentBalance = ROUND (
@Loan * POWER( (1 + @CompoundingInterest) , @Period )
- ( (@Payment/@CompoundingInterest) * (POWER((1 + @CompoundingInterest),@Period ) - 1) )
,2)
SET @Principal = CASE
WHEN @Period = 1 THEN ROUND((@Loan - @CurrentBalance),2)
ELSE ROUND((SELECT CURRENT_BALANCE - @CurrentBalance
FROM #SCHEDULE WHERE PERIOD = @Period -1),2)
END
SET @Interest = ROUND(ABS(@Payment - @Principal),2)
SET @MortgageDueDate = @MortgagePayDate
INSERT #SCHEDULE(PERIOD, PAYDATE, PAYMENT, CURRENT_BALANCE, INTEREST, PRINCIPAL)
SELECT @Period, @MortgageDueDate, @Payment, @CurrentBalance, @Interest, @Principal
SET @Period = @Period + 1 SET @MortgagePayDate = DATEADD(MM,1,@MortgagePayDate) END
END
SELECT * FROM #SCHEDULE
Tags: t-sql
Categories: SQL Programming