Remove the last word while using left in sql query...I weant to avoid broken words while doing so...
My query as follows:

select top 2 tempJobTitle.id,tempJobTitle.JobRefId,tempJobTitle.JobTitle,LEFT (jobmaster.JobDescription2, 330)+'....' as JobDescription,jobmaster.PostedDate from tempJobTitle inner join jobmaster on jobmaster.JobRefId=tempJobTitle.JobRefId where tempJobTitle.Status='Approved' and jobmaster.ClosingDate >= '2013-03-01' order by tempJobTitle.DateofEntry desc

Recommended Answers

All 8 Replies

The only way I can think of right now is to:

  1. Use LEFT
  2. REVERSE the result of 1
  3. Use CHARINDEX on the result of 2 to search for a space
  4. If 3 returns NULL, 0 or 1 use TRIM else continue with 5
  5. Use SUBSTRING with the result of 4 to remove the last (partial) word
  6. REVERSE the result of 5

can u provide a code illustrating the above...

All capital words are SQL Server functions, just use them in that order.

QUERY ILLUSTRATING WOULD HELP..

Shouting (all capitals) doesn't help, because it's considered very rude. Trying the functions yourself to see what they do (showing some effort) and then trying to use them to solve your problem helps too. You get help when you show effort.

@pritaeas: Aren't you making this too complicated?

LEFT(LEN - PATINDEX(REVERSE))

I was hoping he'd show some effort... to get to know the functions and where to look, but alas, stupid me.

I totally agree about OP, but you used 2 reverse, 1 substring, 1 trim, 1 left.
Probably everything is to be used twice if you really meant it about the if - or a case if you want to do it in 1 select.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.