Replace tags in a string

I ran into a situation today where a client wants to be able to give us a string for a disclaimer, but they want to be able to add tags to the string which, after we work our magic, will be replaced with a value from a table, making the string somewhat dynamic in nature. Here's how I went about doing it.

First, I made a function to house the logic around the tags. I realize there are a few shortcomings here and currently, this means that you can't have a '<' in the text without it being the start of a full tag ending in '>', but there are a few adaptations that I feel would be easy enough to implement if this became problematic. The code is a modified version of a comma-delimited string parsing function I use a lot. Basically what this part does is gets the charindex of the opening and closing brackets and then length between the two. It cuts out the substring between them based on that length and pops it into a table. After it does that, it cuts off everything to the left of that closing bracket (or to be more accurate, it keeps the right side). This repeats until you have a table filled with the contents of all the bracketed terms in the string.



IF OBJECT_ID (N'dbo.TagParse') IS NOT NULL
DROP FUNCTION dbo.TagParse
GO

CREATE FUNCTION dbo.TagParse(@String varchar(max))
RETURNS @TagValues TABLE
(
ident int identity(1,1) primary key clustered,
TagValue varchar(100)
)
AS
begin

declare
@FragText varchar(255),
@OpenIndex int,
@CloseIndex int,
@FragLength int,
@StringLength int

--Get some relevant information about the frament length and position
select
@OpenIndex = CHARINDEX('<', @String) ,
@CloseIndex = CHARINDEX('>', @String),
@StringLength = datalength(@String)
select
@FragLength = @CloseIndex - @OpenIndex

while @CloseIndex > 0
begin

select @FragText = SUBSTRING(@String, @OpenIndex + 1, @FragLength -1)
select @String = RIGHT(@String, @StringLength - @CloseIndex)

insert into @TagValues(TagValue)
select @FragText

select
@OpenIndex = CHARINDEX('<', @String) ,
@CloseIndex = CHARINDEX('>', @String),
@StringLength = datalength(@String)
select
@FragLength = @CloseIndex - @OpenIndex

end

return
end


Once we've got the tags parsed out, we can get into getting those values from a presentation table. For the purposes of what I had to do, it will always come from the same table, but I suppose if one were so inclined, you could do some funky dynamic sql to be able to specify a table name too, but I won't go there (right now). I've built up some basic sample data so you can see this in action, but the idea behind it is to:

1) parse out the string using the above function
2) set a variable = the fact name parsed from the string
3) retrieve the value from the presentation table based on the fact name from step 2

Then, much to my dismay, using a loop, I go through the parsed table and replace instances of the tag with the values I just set. If there's a set based way to do this, I'd love to know it, but I couldn't think of a way that didn't involve dynamic sql and lots of nested replace statements. And given that these tagged values are probably not ever going to be more than a few tags here and there, the strain of RBAR is not too bad.


if OBJECT_ID('tempdb.dbo.#FactSet') > 0 drop table #FactSet
create table #FactSet
(
FactName varchar(100),
FactValue varchar(100)
)

insert into #FactSet (FactName, FactValue)
select 'FactName1', 'Test Value 1' union all
select 'FactName2', '3.14'

declare @Tags table
(
ident tinyint primary key clustered,
TagValue varchar(100)
)

declare
@String varchar(max),
@ident tinyint,
@MaxIdent tinyint,
@FactName varchar(255),
@FactValue varchar(255)

--This is the string that will constitute the disclaimer text they want to chop up
select @String = 'This is a test string. Field 1: , Field 2: '

--Parse the string into its constituent tags
insert into @Tags (ident, TagValue)
select ident, TagValue
from sysmon.dbo.TagParse(@String)

--Get the max ident through @@rowcount so I dont have to actually hit the @Tags table
select
@Ident = 1,
@MaxIdent = @@rowcount

while @ident <= @MaxIdent
begin
--Get the name of the fact name we parsed and assign it to @FactName
select @FactName = TagValue
from @Tags
where ident = @ident

--Get the value from the Fact Set table which corresponds to @FactName
select @FactValue = FactValue
from #FactSet
where FactName = @FactName

--We then replace any instances of the original tag with the value we just got.
--Enclosing the @FactName in <> to simulate the original tag syntax.
select @String = REPLACE(@String, '<' + @FactName + '>', @FactValue)

select @ident = @ident + 1

end

select @string


If anyone who reads this understands what the hell I just did, I'd be happy to hear any ways you think this could be done better.

Number 4

Line-work on my latest piece. P.S. the sternum hurts like a bitch.

MW3

So Modern Warfare 3 came out recently, but I didn't buy it. Something is kind of wrong with online gaming with fps games. It was one thing when these people had to slam their carpel-tunnel ridden little fists into the keyboard to insult you, but now we've got Teamspeak. I'm not so much a fan of getting called a faggot by a 13 year old. If I wanted that, I'd just give an anti-drug lecture at a middle school.

Software Developer Lifecycle

Found a good succinct article about career progression in programming that I thought I'd re-share:

http://www.aaronlowe.net/archive/2011/10/do-you-want-to-be-the-best/

Year 1 – Focus on Reliability/Standardization
Year 2 – Focus on Optimization
Year 3 – Focus on Automation
Year 4 – Focus on lolcats (ok so Kevin didn’t specifically say lolcats)

Is it legal?

After several years not DJing, I decided to get a little DJ mixing console and some software to make some new mixes and play around with making music again. I unpacked my Numark Mixtrack Pro, threw on some Pendulum and Shock One, and started recording. It sounded pretty good. So up it went to Soundcloud. Then I got this email:

It said my mix "...may contain content that is owned or licensed by Warner Music UK (Pendulum, Witchcraft).
As a result, we have paused the upload of your audio for the time being."

Hrm, ok. Maybe it's not going up. But what is legal anyway? At any given time you can find tons of free mixes online, rippable from youtube or from any of 1000 other source on the internet, but I can't post a 20 minute mix on Soundcloud? I found a few good articles, but to summarize it, any webcast or live performance you technically need all the rights from the labels to play their tracks (whether you're being paid or not). Oh well. I guess I just won't go pro with this.

SQL Pivot / Unpivot

In my experience, the PIVOT and UNPIVOT functions in SQL Server are one of those things that you won't ever need until you need them, and then they are invaluable. If you haven't used them, they basically turn columns into rows and vice versa (I'll go into a bit more detail later). Specifically, a pivot turns columns into rows, and rows into columns. Most commonly, these functions are used to present data in a different format for reporting purposes, but there are other applications as well.

There are a few typical examples of usage of pivots, and a good summary can be found in the Books Online for Pivots and Unpivots, but I'll let you read up on that yourself. I'd like to share a real world example of an unpivot usage, and then a tip for navigating the syntax of pivots which I feel make them way easier.

For a procedure I was writing recently, I had a table which looked like this:



The idea is that for the [ctnull] column, you want to have the count of how many columns are not null for the purpose of averaging the fields you actually have data for (i.e. isnull(val1, 0) + isnull(val2, 0) + isnull(val3, 0) + isnull(val4, 0) / ctnull).

This is doable with some ugly case statments and a few other methods, but as soon as it evolves beyond a basic calculation, this gets really hairy really fast, and thus is a perfect application for a pivot (or more specifically in this case, an unpivot to make an individual row for each date and measure:

TheDate Measure value
40000 Val1 87
40000 Val2 21
40000 Val3 33
40000 Val4 null
40001 Val1 82
...

PIVOT SYNTAX
The syntax for these operations can be the most confusing, but I realize that there are 3 basic components to a pivot, each with a specific purpose, and when properly understood make them much more manageable. You have an outer query, a subquery, and the pivot. The below example is from BOL:

--Part 1
SELECT VendorID, Employee, Orders
FROM

--Part 2
(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
FROM pvt) p
UNPIVOT

--Part 3
(Orders FOR Employee IN
(Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt;
GO

Part 1: This defines the aesthetic structure of the result table. In other words, if you want your outputs first column to be employeeId, put that there. If you want subsequent columns along the top to be ice cream flavors, put those across the top. This is mostly responsible for naming and organizing what you want the output to look like. You can use aggregate functions, case statements, or just about anything else you want.

Part 2: This is the actual data and source for the data you want for your pivot. If you want a value to be output or used, it must be in this select. So this would contain your EmployeeIDs, their sales figures, height, weight, ice cream preference, or whatever else you will be using or pivoting on.

Part 3: This contains the actual pivot syntax. While there is still some cryptic syntax in here, once you have realized that this 3rd part is really the only unfamiliar part to the pivot, its much more manageable. You just need the column you want to pivot on, the aggregates, and the IN statement for the valid columns/rows. Spend a little time familiarizing yourself with this portion as it turns out it's really the only non-standard portion.

Once you realize what the function of the first two parts are and how to construct them, it breaks down the big ugly pivot syntax into three familiar and easy to deal with chunks which make the whole thing much more friendly. Hope this helps!

Yellow Fruit

King Soopers usually has a selection of weird fruits and every now and then I pick a random one up, perform an autopsy on it, and generally eat it. This is the story of a big leathery yellow thing.


It's about 7 inches in diameter, bumpy leathery surface and weights about three pounds. The outside skin is rugged and takes a little extra knife pressure to puncture, but once you're in, you can slice through it like butter.


Once open, the first impression is that of a cantelopue, mixed with a watermelon.



After removing the seeds, you can really see how juicy and soft the fruit is.




I sliced it up like I would a canteloupe and ate a few pieces plain. It's a very soft fruit, with a texture almost like a banana. As with the smell, it has a mildly sweet taste like the melon combination I mentioned above.

I opted to make a smoothie with it, and added frozen strawberries to the mix. I don't have a picture of the final smoothie because at the time of writing this, I drank it, and the pics I took were all blurry. The smoothie turned out awesome. Next time I think I'll throw some kiwi in the mix.

Follow your dreams and stuff

Well, I've been programming SQL Server now for the eons that is 4 months, and I gotta say, I've never been happier in my life. For a while after college, I sort of had a fear that I would end up doing something like recruiting for the rest of my life, grinding my teeth through each agonizing second of something I didn't love. Honestly, I got to the point that I was so frightened by the possibility of not loving what I did, that I started really exploring what it was that I wanted to do.

It turns out it had absolutely nothing to do with my degree. I know this is hardly news to the world at large, but apparently school doesn't dictate what you do with your life (or maybe it does, just in that it steers you away from what you went to school for...).

The point is, I took it upon myself to pick up books, subscribe to training websites, write programs and databases, come up with projects and most importantly, actually apply for jobs. It can be easy to feel trapped in a certain flow of life, but it's refreshing to know that hard work and dedication can actually pay off.

SQL Avoiding Subquery Using OVER()

I came across a cool way to avoid using subqueries when you need to get different aggregates in the same result set. Typically, you use a subquery with the aggregate you want, and then select that value for the other aggregates you want to work with. While this doesn't really cause any problems, sometimes it clutters up the code, and as it turns out, can be slightly more costly than other methods.

The code below shows what I mean. I have an example of how you might think to go about doing it, which doesn't work, a method that utilizes a subquery, and a nifty trick you can do using an over() clause with the aggregates.


if OBJECT_ID('tempdb.dbo.#test') > 0 drop table #test
create table #test
(
value int
)

insert into #test (value)
SELECT 1
union all
SELECT 1
union all
SELECT 2
union all
SELECT 5

SELECT * FROM #test



Scenario: we want to see what percent of the time a given value occurs in a set. To do this, we want to divide the count of each value by the count of total values. In the above test data, 1 occurs 50% of the time (2/4) whereas 2 and 5 occur 25% of the time (1/4, 1/4). This is a pretty simple scenario to solve, but there are a few ways to go about doing it. Pay special attention to the [TotalCount] column (aka the denominator of the [Percent] column.


--This query is in valid because the aggregates aren't part of the GROUP BY
SELECT
[Count] = COUNT(1),
[TotalCount] = SUM(COUNT(1)),
[Percent] = COUNT(1) * 100.0 / SUM(COUNT(1))
FROM #test
GROUP BY value

--Subquery option
--This is the way I initially thought of doing it
SELECT
[Count] = COUNT(1),
[TotalCount] = (SELECT COUNT(1) FROM #test),
[Percent] = COUNT(1) * 100.0 / (SELECT COUNT(1) FROM #test)
FROM #test
GROUP BY value

--OVER() option
--This is the cool way that I saw a co-worker do today
--by using an empty OVER() statement, you can avoid using the subquery and actually save some performance costs
SELECT
[Count] = COUNT(1),
[TotalCount] = SUM(count(1)) OVER(),
[Percent] = COUNT(1) * 100.0 / SUM(COUNT(1)) OVER()
FROM #test
GROUP BY value


The over() clause allows you to apply the aggregate over a different window as you see fit, which in this case ends up being over the entire set. The end result is the same but with much less code, and faster.



The relative cost of the subquery method is 59% compared to 41% using the over() clause, which is pretty significant. Toy around with the over() clause and using different aggregate functions. This primarily works because you have to hit #test once for the overall query, and again for the subquery. The method with the over() clause only hits the #test table once, and most of the cost comes from a sort performed by the over(). You can generate some useful figures to work with without having to really delve into any subqueries.

Paracord Bracelet

How many times has this happened to you? You've got some big heavy thing you need to tie to something else, but you just don't have any high tensile strength woven fiber? Well no more! I bought 100 yards of paracord and some plastic clasps to make paracord bracelets! Here's my first one.

SQL Date Ranges

I run across a lot of scenarios in my work where I have to return a result set for a given time period. A simple example is that a website might need closing prices for the last day, week, month, year, etc.


select *
from PricingTable
where thedate is between
case when @DateRange = 'Weekly' then DATEADD(wk,-1,getdate()) --@WeekDate
when @DateRange = 'Monthly' then DATEADD(mm,-1,getdate()) --@MonthDate
when @DateRange = 'Yearly' then DATEADD(yy,-1,getdate()) --@YearDate
end and @CurrentDate --getdate()


Problems arise however because the date logic does not take into account weekends and holidays when the underlying data might not exist. To solve this problem, we have a UDF which essentially does the following, but in lieu of one, here's what you can do.

1. Set @CurrentDate = current date (getdate())
   
2. Set @WeekDate, @MonthDate and @YearDate = to dateadd -1 time period
   
3. Here's where it gets cool. You want to set each time period = to the highest date from the underlying table as long as it is less than or equal to the raw date you selected before.

   select @WeekDate = MAX(thedate)
from PricingTable
where thedate <= @WeekDate

   That way if the date is a valid day of the week, the date stays the same. Otherwise you get the closest date to it that is valid in the table. Depending on the requirements of your site, you can do something similar to say the minimum date where it is greater than or equal to the raw date, to shift the date the other direction
   
4. repeat this for the dates you have, and you then have working date ranges that are guaranteed to work for the underlying data.
   

In some scenarios, this much level of precision doesn't matter, but I've seen a wide range of problems arise from not using a technique like this that can manifest in strange ways, and be difficult to troubleshoot depending on the complexity of the procedure. If the underlying table is indexed on [thedate], it will speed up these seeks as well, and the extra code can save you some big headaches down the line.

EDIT 9/8/2011:
An alternative to this method is to simply say if the day it lands on is Saturday, subtract 1 day or if Sunday, subtract 2 days if you don't care about holidays. There are other function which can be written to account for US holidays, but this method works in the specific scenario where you a) need to account for holidays and b) don't have a means (yet) of accounting for holidays (or if you deal with international data where holidays could vary greatly).

Guacamole of the Gods

This is the guacamole recipe I've made since I was a little kid, and it still blows my mind. Some of the proportions can vary based on your own personal preferences (for example, I'm a garlic fiend, so I usually use a bit more of that), but I'll try to give you a good balanced version.

What you'll need:

• 2 Ripe Avocados


• Juice of half a lime


• 1 tsp Cumin


• 1 tsp Chili Powder


• 3-4 Tbs Salsa (While I wish I could say some obscure random salsa from a mom and pop farm in some obscure city is the best, Pace Chunky medium salsa really works the best in my opinion)


• 2 cloves garlic, pressed through a garlic press. If you don't have a garlic press, you can mince it real fine, but a press tastes way better. Try to avoid using garlic powder

What you do:

1. Cut the avocados in half, remove the pit and scoop their delicious guts into a bowl, mash with a fork.


2. Add all the other ingredient and stir with a fork until evenly blended.

Notes: I've made this in a food processor before to make a bigger batch, but it changes the consistency quite a bit, which personally I wasn't a fan of. Play around with it by adding slightly more or less of various ingredients, or by stirring it longer or shorter for a more pureed or chunky guacamole.

Storage:
Guacamole goes brown really easily. To prevent that, cover it with Saran wrap, and press it down so that it is completely flush with the guacamole, eliminating as many air pockets as possible. This prevents most of the oxygen from reaching the guacamole and oxidizing it.

SQL Server vs. The World

I recently had a conversation with a good friend of mine about the merits SQL Server and other databases, and I felt rather unqualified to really comment on any differences between them. I work with SQL Server on a daily basis, know it well and love it. but at the same time, I got by just fine before I got a cellphone when I was 12, just because I didn't know any better.

As such, I decided to start teaching myself LAMP development as a side project ((L)inux (A)pache (M)ySQL (P)HP for those who don't know). I'ts been a lot of fun, and I'm still far from being able to really speak to the operational differences between the two, but I do have some initial impressions about MySQL.

Pros
- Easy to get started (free download, easy install, up and running quickly)
- For everything I've used it for thus far, it performs, on a bad day, just as well as SQL Server.
- A lot of freely available support software such as a GUI, admin tool, etc
- Easily integrates with LAMP technology (there is a lot of collaboration between the communities of Linux Apache MySQL and PHP so that they flow pretty flawlessly)

Neutral (depending on viewpoint)
- Advanced syntax much different from TSQL,
- More complex configurations can require a strong understanding of the linux terminal and various command line utilities.

Cons
- The amount of documentation and helpful resources available for MySQL (and many of the LAMP technologies) pales in comparison to that available for SQL Server, making learning much more challenging
- Complex data integration tools, and limited BI support.

Conclusion: I don't have one yet. Functionally, it seems to run just as well for all intents and purposes. Since I'm still in the learning process of it, it's significantly more challenging for me than it is for me to do TSQL right now, but that also makes it a lot of fun to work on. If I had to venture a comment comparing the two right now, I would say that MySQL is free, but harder to learn, SQL Server (versions other than Express) is not free, but much more easy to learn. At the risk of sounding cliche, it comes down to personal choice.

Also, I'll freely admit that I chose to work with SQL Server initially because there is more money in it than doing LAMP Development (usually).

At an enterprise level, I have no idea which works better and for what scenarios, but if you are a smaller organization, or just someone looking to do database development, pick what you like, and get really good at it. It's a lot of fun when you start to think in terms of a programming language, be it TSQL, MySQL, or PL/SQL

Stalkers. Now that's true love

Now thats amore baby!

(source: http://www.trulylovable.com/media/738-love-is-to-think.jpg) ... f'n hipsters

Sry

I retract my previous comment about TV shows. It was insensitive and ignorant, and I apologize. Sorry.

I hate everything

HAHAHA omg, did u see wat Paris said on hur show last night? Shes leik "omg poor people should lern how to leik do things better!"

Holy fucking shit, is this what we are idolizing? No wonder our science programs are going down the tubes. No wonder we have to pretend to graduate people who are literally retarded. Because people feel like the opinion of someone born into incomprehensible riches has ANYTHING even remotely intelligible to say about ANYTHING. Fuck, this isn't a commentary about America, this is a commentary about humanity. If you find ANYTHING Paris Hilton does cheeky or entertaining, you should throw yourself off a fucking cliff. If you don't know where to find one, let me know, and I'd be happy to Google that for you

Chasing our own tail

I was watching a documentary about the big bang, quantum entanglement, and other such niftyness, and they said something which has been stated before, but made me think of something neat. Since all the matter in the universe was originally a singularity, and all the energy in the universe is linked together in some way, it made me think peaceful, awe inspired thoughts. When I thought about things like war and violence, it made me think of a dog chasing its own tail. A dog sees something weird out of the corner of his eye, and decides it is his enemy. He then spins around in circles trying to get it, not realize that it's actually itself. In a strange way, thats kind of what violence, racism and war boil down to. People see something they don't understand, so they try to chase it, hurt it, destroy it, not realizing that in reality, it's really the exact same matter and energy as ourselves. Can't help but think if people recognized this and worked together how much wasted energy could be used for something greater.

Hello, My Name Is

If someone gets this at a party, hug them.

IT and Recruiters

Through the process of transitioning from a job in IT Recruiting to my current career as a SQL Programmer, I have gained an interesting perspective on the relationship between IT work and Recruiting work. Sometimes it is a harmonious relationship, and sometimes it is much more detached and meaningless. I had the fortune of working with some of the best recruiters in the business, and also getting to see some of the practices that other firms used. I then of course also got to actually go through an interview for the job I have now. Here are a few tips I have for interacting and getting the most out of your interaction and relationship with your recruiter(s).

1) Spend time on your resume!
This is by far the best thing you can do to be noticed by recruiters. Recruiters have access to tens of thousands of resumes, and can easily run across hundreds in and around the area you are working with they keywords they are looking for. This may sound like beating a dead horse, but clean up your resume before you send it out. Not just for spelling and punctuation errors, but for overall formatting. At my company we would frequently work with candidates to repair their resumes into a tried and true format.

Usually it looked like Name/Contact -> Technical Skills -> Work Experience -> Education/Certification.

USE BULLETS! Nothing is more frustrating than having to read through half of war an peace to try to find out what you have actually been up to at your last job.

LIST DATES OF EMPLOYMENT! Recruiters like to see what you have been up to recently, and specifically in the last 3-6 months. A resume without dates again causes the problem of forcing the recruiter to try to guess at what you have been doing, and that's not what you want.

2) Recruiters are NOT technical!
This ties in very closely with the last point, but recruiters don't really know anything about IT. They know a lot of acronyms, they know a lot of technologies just shy of skin deep, but if you wanted them to define what Inheritence means other than "something to do with Object Oriented programming (whatever that is)", you are SOL.

That said, help us help you. This might sound like an over-simplification, but get those acronyms they know into your resume. Some details about what the nature of the project was (i.e. website, financial reporting tool, etc.), but make sure to tie in any technologies you used, and REPEAT IT! Trust me, I know this can feel stupid when you are typing "wrote T-SQL Code in SQL Server 2008..." for the third time under one job, but it IS true, and it WILL get your resume to pop to the top of the list when a recruiter queries "SQL AND Develop*" into Dice.

3) Humor us
Again, tied into the last point, help the recruiters out once they do notice you and get you on the phone or in person. Be willing to exercise some patience, explain something to them in layman's terms, or not take it personally when you try to explain how a password hash algorithm works and you get a smile and nod back. Recruiters are really just looking for an answer to their questions that aren't simply "yes, I worked with that." A good concise answer to a question posed might not make any sense to them, but they will know that you know what you are talking about.

That said, don't ramble. It only takes about 20-30 seconds for them to believe that you know your stuff. After that, interest will quickly fall off, and can get to the point where you will be written off as a ramble who wont shut up. Clean, Concise, and Friendly.

4) Be prepared to be blown up
If you followed the above steps, you will probably be getting a LOT of phone calls and emails. Good IT people with solid resumes (especially at a more senior level) can easily get multiple contacts PER DAY, especially after you recently post your resume. A new resume on a job board will be swarmed to quickly. I know of many candidates who would set up a googleVoice account temporarily while on the job hunt, and a separate email address. Once they find a job, they can put an auto reply to the account saying something to the effect of "Thank you for your interest, but I am currently happily employed. If I am ever on the job search again, I will contact you.". Simple, separate you from all the insanity of being called all the time, and leaves a good taste in the recruiters mouth.

5) Be respectful
Above all, whatever your opinion of recruiters is (whether you find them annoying, useful, interesting,) be respectful. Recruiters have piles of resumes to get to, and it doesn't take much for them to write you off and move on, but it's not hard to stay on their good side. Show up to interviews well dressed and on time. Be prompt with email responses. Be friendly, professional, courteous, and helpful. This probably sounds like what you would expect FROM your recruiter, and indeed you should. But if you do all the things on this list, and especially are respectful to the recruiters, your name will be praised, and called for all the best positions when they open up.

Hip Hop Groups That Don't Suck

This is just a list (more for my own memory) of hip hop groups that don't suck.

- Blue Scholars
- Jurassic 5
- Ozomatli
- Binary Star
- Psychic Origami

... More to come

Passage of Time at Work

Top image is on a logarithmic scale, while the bottom image is normal scale.

How to provoke an argument

I recently found this moving quote online.