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.

The way you implemented the charIndex function fails when the length of the text in the field is less than the start position.

In the code below, I first set the start position to 15 and run three queries. All three work fine. I then set the start position to 30 and the second query (field name is shortbody_2b) returns nothing. The last query (shortbody_3b) handles the problem.

Using CharIndex and Left functions on a Text column causes this error (sql 7) "Argument data type text is invalid for argument 1 of left function". However, PatIndex (msdn: "PATINDEX is useful with text data types") only accepts 2 arguments, so you can't start it at position 150.

PatIndex "...can be used in a WHERE clause in addition to IS NULL, IS NOT NULL, and LIKE (the only other comparisons that are valid on text in a WHERE clause)."

I have to group the issues of a daily newspaper by month using a field called ISSUE_LABEL that can be 1/1 (for January 1st) to 12/31 (for Dec. 31th) or 7/12 (Jul 12) or 12/7 (Dec 7). I cannot influence the input or structure of the database so I need to work with it.

I am trying to do something fairly simple - I need to select the first word from one field and (preferrably) have that first word inserted into a new field (in Microsoft Access). I was attempting to use this statement:

When you nest CHARINDEX inside the LEFT function you will get error "Msg 536, Invalid length parameter passed to the SUBSTRING function." if the character expression you look for is not part of the string you are looking in. The solution is to add a where clause that ensures the CHARINDEX is greater than zero.

I'm trying to do something simular, but using the RIGHT function and unfortunately I'm not getting any where, although you article has helped me a lot but I'm stuck on adaptin it to display only the last four words (complete) from a database field.

So far I have:

SELECT TOP 1 RIGHT(text, CHARINDEX(' ', text, 30)) as t4 FROM STORY WHERE paid IS NOT Null ORDER BY paid Desc;

If you are more comfortable with Application language, rather than SQL, you might want to do the "splitting" into individual words there, and Insert each one into the database - perhaps via individual INSERT statements (which will be relatively slow, one-by-one from the application, but may be fast enough for your needs), or sending as an XML list or some-such which can be readily imported into a table with some simple SQL statements

I'm trying to do something similiar to how this post started. Instead of 150 characters I want to return the first 30, however I also do not want to cut any words off. So if the word is going to get cut off, leave the word out and just stop at the previous word. This is just one example, the descriptions vary in length.

This was my first attempt:select left (itemdescription1, charindex(' ',itemdescription1,30)) as shortenedfrom inmast

It did not work, and below is why.That’s a good thought but it finds the FIRST space AFTER 30 chars. I want the LAST space BEFORE 30. Also, I hate looking for ‘ ‘ because it can be hard to tell the difference between ‘ ‘ and ‘’, depending on the font. Use char(32) instead. That’s just to make the code easier, there is no functional difference.