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
Re: Remove the last word while using Left() in sql query 80 80

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
Re: Remove the last word while using Left() in sql query 80 80

can u provide a code illustrating the above...

Re: Remove the last word while using Left() in sql query 80 80

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

Re: Remove the last word while using Left() in sql query 80 80

QUERY ILLUSTRATING WOULD HELP..

Re: Remove the last word while using Left() in sql query 80 80

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.

Re: Remove the last word while using Left() in sql query 80 80

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

LEFT(LEN - PATINDEX(REVERSE))

Re: Remove the last word while using Left() in sql query 80 80

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

Re: Remove the last word while using Left() in sql query 80 80

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 1.19 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.