Trim a word from all rows returned

• Lamees Ayman

  Grasshopper

  Points: 22

  More actions

  November 16, 2006 at 6:35 am

  #76610

  Hi all,

  I have a returned result as follows:

  Computer and Mathematical Occupations

  Healthcare Practitioners and Technical Occupations

  Management Occupations

  Office and Administrative Support Occupations

  what I want to do is to make the sql query return these data without the word "Occupations" which is at the end of statement.

  Is there a sql function can do so?

  Thanks in advance.

• ijaz

  SSCommitted

  Points: 1912

  More actions

  November 16, 2006 at 6:56 am

  #672251

  Hi,

  Yes you can used the replace function for this purpose.

  SELECT REPLACE(FieldName,'Occupations','')

  Hope this will slove your problem

  Cheers

  cheers

• Lamees Ayman

  Grasshopper

  Points: 22

  More actions

  November 16, 2006 at 7:02 am

  #672257

  Thanks alot.

  It ran correcly

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  November 16, 2006 at 7:36 am

  #672281

  Ya but it works only if the word occupation is only found once in the sentence. 

   

  You might want to add this where condition :

  SELECT CASE WHEN FieldName LIKE '% occupation' THEN LEFT(FieldName, LEN(FieldName) - 11 /* length of occupation.. adjust depending on spaces*/

  ELSE FieldName