String extraction

• jahnavidutt

  Newbie

  Points: 9

  More actions

  May 2, 2012 at 9:33 am

  #255252

  Hi friends,

  From the given string I need to extract the right most value after the '/'

  example: given string

  '/Customer Service2/CS Team - CareFirst Connect'

  Need to get "CS Team - CareFirst Connect"

  2) ex: /Enrollment Analyst/Enrollment Analyst 1

  Needed:Enrollment Analyst 1

  3) ex: /Enrollment Technician/Enrollment Technician 9/Enrollment Technician 8

  needed: Enrollment Technician 8

  Thanks for help.

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  May 2, 2012 at 9:40 am

  #1482504

  How about this?

  DECLARE @Table Table ( String VARCHAR(100))

  insert into @Table values

  ('/Customer Service2/CS Team - CareFirst Connect')

  ,('/Enrollment Analyst/Enrollment Analyst 1')

  ,(' /Enrollment Technician/Enrollment Technician 9/Enrollment Technician 8')

  SELECT Extract = REVERSE ( LEFT ( CrsApp.Rvrs, CHARINDEX( '/' , CrsApp.Rvrs)-1) )

  FROM @Table T

  CROSS APPLY (SELECT REVERSE(T.String)) CrsApp (Rvrs)

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  May 2, 2012 at 9:44 am

  #1482510

  Basically came up with the same thing as ColdCoffee:

  DECLARE @TestStr VARCHAR(128);

  SET @TestStr = '/Customer Service2/CS Team - CareFirst Connect';

  SELECT REVERSE(LEFT(REVERSE(@TestStr),CHARINDEX('/',REVERSE(@TestStr))-1));

  SET @TestStr = '/Enrollment Analyst/Enrollment Analyst 1';

  SELECT REVERSE(LEFT(REVERSE(@TestStr),CHARINDEX('/',REVERSE(@TestStr))-1));

  SET @TestStr = '/Enrollment Technician/Enrollment Technician 9/Enrollment Technician 8';

  SELECT REVERSE(LEFT(REVERSE(@TestStr),CHARINDEX('/',REVERSE(@TestStr))-1));