Returning complete words from a substring

By Bill Graziano on 9 February 2001 | 20 Comments | Tags: SELECT


melanie writes "I am trying to return a string using Select *, left (newsitembodytext, 150) . . . As you can guess it is returning incomplete words. I need the last word to be complete any ideas?" I think we can put some string functions to use and solve this one.

Just for completeness, the entire text of Melanie's query is:
Select *, left (newsitembodytext, 150) as shortbody 
from tbl_industry_news 
where active = 1 
and Communications = 1 
order by listingNumber

On to those sting functions! You started out using the LEFT function which a very good string function. Your example will return the first 150 characters starting at the left of the string. The RIGHT function is very similar but will return a given number of characters starting at the right (or end) of a string.

We're going to start with a function called CHARINDEX. The syntax of CHARINDEX is:

CHARINDEX(expression1, expression2 [, start_location])

CHARINDEX returns the start position of expression1 in expression2. If we ran this little snippet of code:

SELECT START = CHARINDEX('yak', 'I am the Yak')

we'd get something like this:

START       
----------- 
10

(1 row(s) affected)

We can also use the start_location parameter to start our search at a given position. Let's say we were searching for the first space character after the sixth character in our sample string. Our query might look like this:

SELECT START = CHARINDEX(' ', 'I am the Yak', 6)

and our result would look something like this:

START       
----------- 
9

(1 row(s) affected)

We can now find the first space that falls after any number of characters that we want. Let's see if we can start rewriting that SELECT statement. First, we'll have the original and then the enhanced version. Note that I shortened it to 20 characters for easier reading.

Select 	LEFT (newsitembodytext, 20 ) as shortbody 
from 	tbl_industry_news 

Select 	LEFT (newsitembodytext, CHARINDEX(' ', newsitembodytext, 20) ) as shortbody 
from 	tbl_industry_news

And the results from the two queries:

shortbody            
-------------------- 
Wild Yak trading rea
Agriculture is key t

(2 row(s) affected)

shortbody                                
---------------------------------------- 
Wild Yak trading reached 
Agriculture is key to 

(2 row(s) affected)

You'll note that the first result set has the string truncated in the middle while the second has a nice break in between words. You should be able to use this to get the results you want. Enjoy.

Discuss this article: 20 Comments so far. Print this Article.

If you like this article you can sign up for our weekly newsletter. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

Related Articles

Joining to the Next Sequential Row (2 April 2008)

Writing Outer Joins in T-SQL (11 February 2008)

How to Use GROUP BY with Distinct Aggregates and Derived tables (31 July 2007)

How to Use GROUP BY in SQL Server (30 July 2007)

SQL Server 2005: Using OVER() with Aggregate Functions (21 May 2007)

Server Side Paging using SQL Server 2005 (4 January 2007)

Using XQuery, New Large DataTypes, and More (9 May 2006)

Counting Parents and Children with Count Distinct (10 January 2006)

Other Recent Forum Posts

How to find clashes (1 Reply)

how to get Distinct id with same value (3 Replies)

1 Server multiple instances or 1 instance or even (0 Replies)

how to display all columns of distinct rows (1 Reply)

Stored Procedure for paging 2000 vs 2005 (1 Reply)

Turning Rows into Columns (2 Replies)

Encryption and decryption of column values (1 Reply)

Split String & Insert Into Table Rows (3 Replies)

Subscribe to SQLTeam.com

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -