Extracting text from a longer string

• Paul.Mitchell.

  Ten Centuries

  Points: 1131

  More actions

  March 23, 2006 at 7:01 am

  #171007

  I have a field called ReportPath containing data like this:

  /DL047 London Ship Managers/Call Records Invoice Back Up

  /DL047 London Ship Managers/Invoices by Vessel

   

  How do I create and alias to get just the left-most text between the two "\" characters so it returns just

  DL047 London Ship Managers

  DL047 London Ship Managers

   

  Anyone able to help an amateur?  Thanks in advance...

• mngong

  SSCertifiable

  Points: 6167

  More actions

  March 23, 2006 at 7:58 am

  #628345

  Do all the entries look like this or some have the slash and others do not

• David Burrows

  SSC Guru

  Points: 65142

  More actions

  March 23, 2006 at 7:59 am

  #628346

  SUBSTRING([ReportPath],2,CHARINDEX('/',[ReportPath],2)-2)

  Is the first '/' always in column 1 ?

  Far away is close at hand in the images of elsewhere.
  Anon.

• David Burrows

  SSC Guru

  Points: 65142

  More actions

  March 23, 2006 at 8:00 am

  #628350

  If not then

  SUBSTRING([ReportPath],CHARINDEX('/',[ReportPath])+1,CHARINDEX('/',[ReportPath],CHARINDEX('/',[ReportPath])+1)-CHARINDEX('/',[ReportPath])-1)

  Far away is close at hand in the images of elsewhere.
  Anon.