select specific part of a string at a variable pos

• ianfraser

  SSC Rookie

  Points: 38

  More actions

  February 3, 2003 at 10:12 am

  #80199

  I am needing to select a value from within a text column - a bit of a new one for me and it's proving to be a bit of a teaser.

  I want to select only the last value in the string where the value is preceded with the string "Outlays recovered : "

  examples:

  NARRATIVE (text,Null)

  ----------------------------------------------------------------------------

  -------------

  Fees recovered : £705.00 VAT recovered : £123.38 Outlays recovered :

  £411.25

  Fees recovered : £20.00 VAT recovered : £3.50 Outlays recovered : £63.51

  Fees recovered : £20.00 VAT recovered : £3.50 Outlays recovered : £63.51

  Fees recovered : £20.00 VAT recovered : £3.50

  Fees recovered : £10.00 VAT recovered : £1.75

  Fees recovered : £15,000.00 VAT recovered : £2,625.00 Outlays recovered :

  £240.00

  The results I require :

  411.25

  63.51

  63.51

  240.00

  Any help would be appreciated.

• Greg Larsen

  SSC-Insane

  Points: 20961

  More actions

  February 3, 2003 at 10:30 am

  #447841

  You should be able to use charindex, substring and len functions to do this. Try something like:

  Select substring(column_to_seach,charindex('Outlays recovered :',column_to_search) + 20,len(column_to_search))

  Gregory Larsen, DBA

  If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

  Gregory A. Larsen, MVP