I typically don’t blog about other people blogs, because quite frankly I don’t like giving summations of the work someone else has provided. I just don’t think it is a very good practice, but every now and then a quick post about something I have seen or found, warrants a brief blog about it. Needless to say though, one must give the original creator their proper credit.

At any rate, today I thought that I would share something that I have seen recently. One that I never really thought about trying! It is using the TSQL Replace function to count the amount of words in a string, but I can definitely see its usefulness. So today, I want to show you a quick script that Andy Warren recently posted in his SQL Share: String Handling Functions – Part 5. There were quite a few samples on the video, so if you are looking for video content on the REPLACE function, I would encourage you to go and watch the video.

So, here is a modified version of his script that I want to share with you.

Script 1: Andy Warren’s Word Count Script (slightly modified by me)

DECLARE @DarthVader VARCHAR(500)

SET @DarthVader = 'Luke. I am your father!'

SELECT LEN(@DarthVader) - LEN(REPLACE(@DarthVader, ' ', '')) + 1 AS WordCount

The bread and butter of this script is found within the difference of length before replacing any blank spaces and the length after the spaces are removed. He then adds a 1 to offset that the final word typically doesn’t have a space at the end of it. As he mentions, it’s not always 100% accurate, but it sure is a fun way to play with words. J Thanks for sharing that with us Andy!

I hope that you have enjoyed this post. Also, if you don’t already, please be sure to follow me on twitter at @briankmcdonald. Also note that you can subscribe to an RSS feed of my blogs.

Brian K. McDonald, MCDBA, MCSD
Business Intelligence Consultant – Pragmatic Works

Email: bmcdonald@pragmaticworks.com | Blogs: SSRSGeek | SQLServerCentral | BIDN

Twitter: @briankmcdonald