Member Avatar for korathualex

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
Edited by pritaeas because: Added markdown
  • 3 Contributors
  • 8 Replies
  • 2K Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by adam_k
Member Avatar for pritaeas

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
Member Avatar for korathualex

can u provide a code illustrating the above...

Member Avatar for pritaeas

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

Member Avatar for korathualex

QUERY ILLUSTRATING WOULD HELP..

Member Avatar for pritaeas

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.

Member Avatar for adam_k

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

LEFT(LEN - PATINDEX(REVERSE))

Member Avatar for pritaeas

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

Edited by pritaeas
Member Avatar for adam_k

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.