Convert Price that comes in as a string into a float

[nadine]

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

$12.09

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

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

Results:
12.09

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.