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.