Comparing and replacing Text Data in SQL Server

When NTEXT or TEXT data is longer than allowed for varchar(max), comparing or replacing data in the string is difficult. This is made easy by use of Substring and Datalength functions in TSQL.

Below is a sample script where I had to load delta of note data. To do this I had to understand which notes were actually changed in the note field not just viewed or accessed by a user. Initial Data Load notes are in the _IDL table and delta is in the base table. If the replace returned empty or null there was not a change, otherwise the resulting note was different and had to replace the previously loaded note record.

The below is what was used to analyze the delta.

select T1.Id, T1.AccountId,T1.OpptyId,T1.ContactId,T1.CustomerServiceId,T1.ProjectId,T1.Subject,T1.Description
from T_SOD_STG_Notes T1, T_SOD_STG_Notes_IDL T2 WHERE T1.Id = T2.Id
AND Replace(SUBSTRING(T1.Description,1,DATALENGTH(T1.Description)),SUBSTRING(T2.Description,1,Datalength(T2.Description)),'') IS NOT NULL
AND Replace(SUBSTRING(T1.Description,1,DATALENGTH(T1.Description)),SUBSTRING(T2.Description,1,Datalength(T2.Description)),'') <> ''

Of course, I could put the exact same replace(substring(...,Datalength(...))) logic into the select and just selected the delta note as well.

My Best Hummus

1 can (15 oz) garbanzo bean (chick peas) drained*

1/8 cup tahina paste

1/8 cup extra virgin cold pressed olive oil

1/2 tsp kosher salt

1/4 tsp paprika

1/8 tsp cumin

half a fresh lemon

some white pepper

crushed red pepper (garnish)

I usually make a double batch.

Instructions. Drain the beans, conserving the "juice." Add the beans, tahina, olive oil, salt, paprika, cumin into your blender. Squeeze the juice out of the lemon. Dash some white pepper in on top. Grab a wooden spoon or silicon (or rubber) scraper. Turn on the blender at it's slowest setting. Gentle push from the edges into the center just staying on top of the ingredients. Everything will blend. If it's too chunky add either (a) more olive oil (my preference), or (b) some of the "juice" you conserved.

I blend mine in my Kitchen Aid blender until I get to second highest setting and leave it on until the humus is light and fluffy; almost whipped. Everyone loves. Lately I've been adding some fresh garlic. Also, a teaspoon of truffle oil instead of garlic would be a gourmet touch.

Keep it simple. Serve directly on a plate in a big dollup drizze with a touch of olive oil and garnish with crushed red pepper. I like to use barbari bread from my local mediterranean market and it can also be found at Sam's. Pita bread is fine. Try your favorite flat bread. I cut mine in 3 inch square, warm in my counter-top toaster then cut into triangles.

Enjoy!