I arranged the solution in a verbose manner so that the original poster could follow the steps I used to parse the data. The problem with that data is that the position of the desired price is almost random. Based on the OP, I had to make an assumption about the business rules involved with this data. That assumption is that the first decimal from the RIGHT was the value he wanted to show as the price. But sometimes there is more than one decimal in the string, sometimes none, or sometimes at the first position. Also, his data did not have a primary key so I created one for my own use with one of the first ROW_NUMBER invocations.
I'm posting a revised version of the script below. To make the script more compact, I went ahead and added a PK to the data so I would have that to work with and joined as many rows as possible with a single query. I've used both a WITH form of query and a query with subquery that accomplish the same thing in this case.
As for the RANK function. Since some of the rows had more than one "candidate" price, I used RANK to put them in the correct order with the value closest to the right being ranked higher than one to the left of it.
;WITH sampledata AS
(
SELECT * FROM
(VALUES
(1,'SBA 60 OFF 19.99 NOW 7.99'),
(2,'SBA 50 OFF 99.99 NOW 49.99'),
(3,'ST. PATRICK 19.99'),
(4,'QC CUSTOM FLORAL'),
(5,'45.23 ST. PATRICK'),
(6,'QC CUSTOM FLORAL $ 43.34')
) DATA (ID,PriceText))
,
ctePrices AS
(
SELECT DISTINCT
ID
,ItemNumber
,REVERSE(Item) AS Element
,s.PriceText
,RANK() OVER (PARTITION BY PriceText ORDER BY ItemNumber) AS [Rank]
FROM
sampledata s
CROSS APPLY
dbo.DelimitedSplit8K(REVERSE(s.PriceText),' ') AS dsk
CROSS APPLY
dbo.Tally t
WHERE
N <= dsk.ItemNumber
AND CHARINDEX('.',REVERSE(dsk.Item)) > 0
AND ISNUMERIC(REVERSE(dsk.Item)) = 1
)
SELECT
s.ID
,s.PriceText
,cte.Element
FROM
ctePrices cte
RIGHT OUTER JOIN
sampledata s
ON cte.PriceText = s.PriceText
WHERE
cte.[Rank] = 1
OR cte.[Rank] IS NULL
ORDER BY
s.ID
;WITH sampledata AS
(
SELECT * FROM
(VALUES
(1,'SBA 60 OFF 19.99 NOW 7.99'),
(2,'SBA 50 OFF 99.99 NOW 49.99'),
(3,'ST. PATRICK 19.99'),
(4,'QC CUSTOM FLORAL'),
(5,'45.23 ST. PATRICK'),
(6,'QC CUSTOM FLORAL $ 43.34')
) DATA (ID,PriceText))
SELECT
s.ID
,s.PriceText
,cte.Element
FROM
(
SELECT DISTINCT
ID
,ItemNumber
,REVERSE(Item) AS Element
,s.PriceText
,RANK() OVER (PARTITION BY PriceText ORDER BY ItemNumber) AS [Rank]
FROM
sampledata s
CROSS APPLY
dbo.DelimitedSplit8K(REVERSE(s.PriceText),' ') AS dsk
CROSS APPLY
dbo.Tally t
WHERE
N <= dsk.ItemNumber
AND CHARINDEX('.',REVERSE(dsk.Item)) > 0
AND ISNUMERIC(REVERSE(dsk.Item)) = 1
) cte
RIGHT OUTER JOIN
sampledata s
ON cte.PriceText = s.PriceText
WHERE
cte.[Rank] = 1
OR cte.[Rank] IS NULL
ORDER BY
s.ID