Tom Muck's Blog
News and Views
SQL function to chop a field by number of words
Tuesday, June 21, 2005 11:30:16 PM
I was prompted by a question on the CMX forums today to finally break down and write a function to return a number of words from a database field, which I've been meaning to do for a long time. There are script examples on the web for ASP and ColdFusion code to truncate a specific database field to a certain number of words (split at the word rather than mid-word, as the LEFT function does), but there is no easy way to do it in SQL, unless you use a loop. The following function will truncate any field to a specific number of words. Pass in the string you want to parse, and the number of words to return.
CREATE FUNCTION fnGetNumberOfWords (
RETURNS varchar(8000) AS
DECLARE @currentword varchar(8000)
DECLARE @returnstring varchar(8000)
DECLARE @wordcount int
SET @wordcount = 0
SET @returnstring = ''
SET @currentword = ''
SET @stringToSplit = ltrim(rtrim(@stringToSplit))
Declare @index int
WHILE @wordcount < @numberOfWords AND len(@stringToSplit) > 0
Select @index = CHARINDEX(' ', @stringToSplit)
if @index = 0
SELECT @currentword = ltrim(rtrim(@stringToSplit))
SELECT @wordcount = @numberOfWords
IF (len(@stringToSplit) - @index > 0) BEGIN
SELECT @currentword = ltrim(rtrim(LEFT(@stringToSplit, @index-1)))--the new shortened string
SELECT @stringToSplit = RIGHT(@stringToSplit,LEN(@stringToSplit) - @index) -- the rest
SELECT @returnstring = @returnstring + ' ' + @currentword
SELECT @wordcount = @wordcount + 1
SET @returnstring = LTRIM(@returnstring)
Call it like this:
SELECT dbo.fnGetNumberOfWords(MyField, 10) FROM mytable
(returns first 10 words from MyField)
The advantage to doing it in the database rather than on the web page, is that you are only returning a small portion of the field to the web page, rather than the entire field. This can speed up the query. A few preliminary tests show that the smaller number of words you return, the quicker the query will execute. In other words, if your query returns a field that can contain up to 8000 characters in it (like a blog entry, for example) and you only need the first 50 words for a summary, the query to return the 50 words will be faster than a query that returns the whole field. Also, your scripted page will execute faster because it is simply displaying the field and not performing any further logic, looping, or parsing on the field.
I talked about user-defined SQL functions in one of my articles at Community MX as well:
Using CSV Strings in SQL Server: Part 2
Note that the function does not work on text or ntext data types. I hope you find it useful.
Cross posted at CMXtraneous
Category tags: Community MX, Dreamweaver, ColdFusion, SQL
Posted by Tom Muck
Add comment | View comments (5) | Permalink | Trackbacks (0) | Digg This
Before posting comments or trackbacks, please read the posting policy.