Extract money ranges from string help

[angiem]

I need to run an update statement to fill two new fields call minvalue and maxvalue (both floats) with values that are stored in a string field as "A $0.00 - $1000.00"

So that the minvalue would be 0.00 and maxvalue would be 1000

Any help would be appreciated

 

[simian336]

This should show you what you need. Just create an update statement out of it. Change test to your field name.

select
CHARINDEX('$',test),
CHARINDEX('-',test),
CHARINDEX('$',test,CHARINDEX('-',test)),
SUBSTRING (test, CHARINDEX('$',test)+1, CHARINDEX('-',test)-CHARINDEX('$',test)-2),
SUBSTRING (test, CHARINDEX('$',test, CHARINDEX('-',test))+1, 50),
cast(SUBSTRING (test, CHARINDEX('$',test)+1, CHARINDEX('-',test)-CHARINDEX('$',test)-2) as float),
cast(SUBSTRING (test, CHARINDEX('$',test, CHARINDEX('-',test))+1, 50) as float)
from dbo.Table_1

Simi