Convert Price that comes in as a string into a float

Let’s say you have data being ingested and you have a field, price, that looks like this:


To convert it into a float, you can write this query:

SELECT AVG(try_cast(REGEXP_REPLACE('$12.09', '[^\d.]') as float)) avgprice


You can do this with Rockset’s Query Based Field Mappings (QBFMs) when you create a Rockset Collection. QBFMs allow you to save on runtime compute, because you’re doing the transformations at the time of ingestion.