Fun With Palindromes – Part 1

I was thinking about algorithms yesterday afternoon – yes, I am a strange one – and one thought led to another and soon I found myself contemplating palindromes – words or phrases that read the same forwards and backwards, such as “racecar” or Napoleon’s famous – albeit apochryphal – “Able was I ere I saw Elba!”  Specifically, I was wondering what would be the most efficient way to test a string’s palindromicity using T-SQL.

Immediately I realized that this algorithm will need to accomplish two different things.  I first need to remove all non-alphabetic characters from the string I am testing, because while “able was I ere I saw Elba” is palindromic even leaving the spaces intact, this will not work for other well-known palindromes such as “A man, a plan, a canal, Panama!”  Then the second task is to check that the remaining string is the same front-to-back as it is back-to-front.

With the help of Elder’s Dead Roots Stirring album I set out to find the most efficient T-SQL code to accomplish this task.  My plan was to avoid resorting to Google for the answer, but perhaps in a future post I will go back and compare my solution to the best one I can find online.  For this first post in the series I will tackle only the first task of removing the non-alphabetic characters from the string.

I’ll readily admit to not being well versed in the use of regular expressions, so in order to meet my Google-free objective I’ll have to find another solution for now.  Let’s start by seeing what we can do with the famed “A man, a plan, a canal, Panama!”  Because I know exactly which characters are in this string, I can use the brute-force method of nested REPLACE() functions to parse them out – see line 11 below.  (You may want to use the Toggle Line Wrap or Open Code In New Window options to see it better.)




Of course I may not always have the luxury of knowing the contents of the string beforehand.  I do know that the characters I want to keep will fall within the range of A-Z (ASCII values 65-90) or a-z (ASCII values 97-122).  My plan is to step through the string one character at a time, but instead of using a WHILE loop, I’ll instead use the tally table (a table of sequential numbers) in my Admin database like so:






If for some reason you don’t have or are not able to add a tally table to your system, you can use the ROW_NUMBER() function against the sys.objects DMO instead:


You can put this code into a common table expression and combine it with the second query:






Of course, separate rows for each character don’t help us very much.  Let’s use the CONCAT() function to put them back into a single string:




This method works great to parse a single string, but I will have a whole table of potential palindromes to parse.  Historically, I would usually use the STUFF() function with FOR XML PATH to reassemble the strings like this:




I’ve always found using this method to be a bit non-intuitive and fiddly.  Thankfully SQL Server 2017 has added a new function called STRING_AGG() that we can use instead:




Wow, that’s a lot of queries, so I’ll bring part one of this series to a close.  I look forward to seeing some other solutions in the comments below.  In part two, we’ll compare the performance of these methods across both a single string, and a whole table of them!


I’m Speaking – December 2017

After a relatively light speaking schedule in November – just PASS Summit this year – I’ll be back with a vengeance in December.  I’ve got three sessions lined up, and all in venues in which I’ll be presenting for the first time!

I’ll be kicking the month off bright and early on Friday, December 1st with the winter edition of the GroupBy conference.  This is a virtual conference held over the first two Fridays in December, with ten sessions voted in by the community.  I was lucky enough to have my SQL Server R Services session selected this time around, and I hope to “see” you all there at 10:30 am EST (17:30 UTC).

I’ll be following that up with a trip to the nation’s capital the following week to present the same session at SQL Saturday Washington DC on Saturday, December 9th.  I was able to attend last year to see some great presentations, but this year the organizers have made the mistake of asking me to speak too!  I’m looking forward to following the excellent Kevin Feasel [b|t] once again as our sessions complement each other’s quite well.

For my final speaking slot of the year, I’ll be presenting virtually to the Orange County BIG PASS group headed up by my friend Mickey Stuewe [b|t].  I’ll be dusting off an older presentation on implementing row-based security through a single custom solution that works for both SSRS and SSAS.  If you’ll be in the Lake Forest area on Thursday, December 14th, please register on their Meetup group and join me for this one.

What a great way to close out another busy year!


T-SQL Tuesday #96: Folks Who Have Made A Difference

This month’s T-SQL Tuesday is hosted by Ewald Cress [b|t], who has invited people to give public shout-outs to those who have helped them in their career or made a meaningful contribution to their “life in the world of data.”

I would like to express my gratitude to two people in particular:  Steve Jones [b|t] and Grant Fritchey [b|t].  The guidance and encouragement that these two have given me have significantly boosted my presentation skills and confidence, and in turn transformed my career as a data professional.  Let me tell you a bit more.

Steve has been very supportive and encouraging since I began speaking at SQL Saturday events.  I remember one presentation in Denver particularly where I wasn’t happy with how it had gone, but Steve was kind enough to say how much he liked the way I’d handled the demo portion, and even recommended to a veteran speaker that he could do worse than follow my example.  You can imagine what a boost to my speaking confidence that was!  He was also the first person to really push me to submit my R session to Summit last year when I didn’t think I was quite ready yet to step up to that level.  Thank you for your backing and inspiration, Steve!

Grant attended a presentation of mine in Albany, and I must admit I was a bit unnerved to have one of the “big names” of SQL in my session.  Afterwards he  provided me with a comprehensive set of pointers including the things I did well and those with which I needed improvement.  This list was instrumental in improving my speaking skills, and is the kind of valuable feedback that is rare to receive.  The really amazing thing to me was that he prepared this list for me while on vacation with his family!  Thank you for your generosity and guidance, Grant!

To Steve and Grant, I can’t thank you enough for the impact you’ve had on my career.  Not only have you helped me grow, you’ve also shown me a model for how I can help others in the SQL community grow too.  Now hopefully I can do the same for someone else on their journey.

SQL Train 2017

One of my highlights of PASS Summit season was the SQL Train, which ferried me and 35 fellow SQL Server nerds from Portland, OR to Seattle, WA.  This is an annual event for those traveling from SQL Saturday Oregon to PASS Summit, and this was the second year for which I was one of the organizers.

In 2015, the train had 20 loud and unruly SQL passengers, much to the chagrin and annoyance of the other 16 people stuck with us in the car!  Last year Mickey Stuewe [B|T] and I decided to organize the event more formally to see if we could get 36 people on board – enough to fill our own, dedicated train car – and it was a great success.

This year, Mickey stepped back from the organizing duties, leaving it all up to me!  Things ran very smoothly this time around.  Almost all of the passengers paid up on time, and everyone showed up at the correct train station right when they were supposed to do.  The only minor wrinkle was that a few folks had to drop out in the week before the trip, but we were able to fill their seats quickly with people from the wait list.

SQL Train 1
Picture courtesy Slava Murygin [B|T]
This year the train had SQL passengers from all over the globe: Australia, Canada, Iceland, the Netherlands, New Zealand, Norway, Portugal, South Africa, and from at least ten states within the U.S!

Of course when this many data professionals get together, especially after a full day of learning and presenting at SQL Saturday Oregon, we sometimes have to let off a little steam.  In fact, I’m not sure I’ve ever seen Peter Kral [T] quite as happy as when he produced this bottle of Irish cream:

SQL Train 2
Picture courtesy Cathrine Wilhelmsen [B|T]
We also did something a little different this year and charged a slight premium to the cost of the ticket, which enabled us to purchase some snacks and beverages for the car.  Many thanks to Bob, Dan, Heidi, Jared, Peter, and Rob for their help in acquiring these items and carrying them on to the train!  Here’s how that money was spent:

I’m already looking forward to next year’s SQL Train, which will be on November 4th, 2018.  With any luck we will get enough interest to fill two full train cars this time!