I don't know if this is any better than Luis' code above, but it gives you another alternative way of looking at the problem.

;WITH sampledata AS

(

SELECT * FROM

(VALUES

('SBA 60 OFF 19.99 NOW 7.99'),

('SBA 50 OFF 99.99 NOW 49.99'),

('ST. PATRICK 19.99'),

('QC CUSTOM FLORAL'),

('45.23 ST. PATRICK'),

('QC CUSTOM FLORAL $ 43.34')

) DATA (PriceText))

,

cteElements AS --split the strings and order the rows from right to left

(

SELECT

ROW_NUMBER() OVER (ORDER BY PriceText) AS ID

,ROW_NUMBER() OVER (PARTITION BY ItemNumber ORDER BY ItemNumber) AS RowNum

,ItemNumber

,REVERSE(Item) AS Element

,s.PriceText

FROM

sampledata s

CROSS APPLY

dbo.DelimitedSplit8K(REVERSE(s.PriceText),' ') AS dsk

)

,

ctePrices AS --find elements that appear to be a price

(

SELECT

ID

,RowNum

,ItemNumber

,Element

,N

,PriceText

FROM

cteElements e

CROSS APPLY

dbo.Tally t

-- these filters may need to altered to fit the data

WHERE

N <= e.ItemNumber

AND CHARINDEX('.',e.Element) > 0

AND ISNUMERIC(e.Element) = 1

--yes, ISNUMERIC is flawed. But it might be OK here.

--I'd consider using a validation function.

)

,

cteRanking AS --put the elements in some useful order

(

SELECT

ROW_NUMBER() OVER (PARTITION BY PriceText ORDER BY ID) AS RowNum

,ROW_NUMBER() OVER (PARTITION BY PriceText,Element ORDER BY ID) AS ItemNum

,Element

,PriceText

,ID

,RANK() OVER (PARTITION BY PriceText ORDER BY ID) AS [Rank]

FROM

ctePrices

)

SELECT DISTINCT

cte.Element

,s.PriceText

FROM

cteRanking cte

RIGHT OUTER JOIN --this join is to bring back in any text that has no price

sampledata s

ON cte.PriceText = s.PriceText

WHERE

RowNum = 1

OR RowNum IS NULL