Text to Floating Decimal Issue

I would recommend trying to understand the problem a little better before fixing it in that particular way. The error message you're getting back should include an example of a failing value. Something like "Failed to parse text '$1.23' as type…", or perhaps "Failed to parse text '#VALUE!'…". If we figure out exactly what's failing to parse, we can fix the problem in a more focused way.

The expression CAST(REGEXP_REPLACE(x, '[^0-9.]', '') AS DECIMAL) will replace any character in x that isn't a digit or a period with the empty string (effectively deleting it) and then convert the resulting string to DECIMAL, which is called "Fixed Decimal" in other places. There are a few issues with this.

For one, there are other characters that may be present in valid numbers coming from Excel which will be quietly deleted, resulting in incorrect values that may appear correct. Here are some valid floating-point number representations that will be successfully but wrongly processed by this expression:

1. -5.5 will be parsed as 5.5

2. 1.6E+3 will be parsed as 1.63

If you know your data well, then these concerns may not apply. Perhaps there are not and never will be negative values, and you know the values are never represented in scientific notation (which, although surprising, is actually the standard way of representing floating-point numbers beyond a certain number of digits).

That said, in most cases I would strongly recommend at least modifying the regular expression from [^0-9.] to [^-+0-9.eE] to cover all the valid floating point values (besides the NaN values, but it's less likely you have those in your data).

But if it were me, I'd figure out specifically why the error is occurring and fix it in a focused way. For example, if the problem is that Excel error values like #VALUE! are in your data, you could get rid of all of them with an expression like CASE WHEN STR_STARTS_WITH(Ad_fees, '#') THEN NULL ELSE CAST(Ad_fees AS DOUBLE) END (use DOUBLE if you want a floating-point number and DECIMAL if you want a fixed-point decimal). Or, if the problem is whitespace characters, you can use CAST(SQUASH_WHITESPACE(Ad_fees) AS DOUBLE). If you have dollar signs, as I'm sure you already know, you can use REPLACE(Ad_fees, '$', '') (be careful with $ in a regex; it has a special meaning so you need to escape it. REPLACE is always better than REGEXP_REPLACE if you just want to replace a single static string or character). If you can't figure out what's in your data that's causing the problem, you could do STR_TO_CHARNAMES(Ad_fees) and export the result and look through it for anything surprising. That will surface any weird, hard-to-spot things in your string like special whitespace characters or Unicode characters that look the same as common characters but aren't.

The advantage of taking the extra time to do it this way is that if a value that you didn't expect does show up in your data, the system will tell you about it instead of quietly dealing with it. That's a tradeoff, of course. Quietly dealing with it might be exactly what you want. But do keep in mind that if you use a simple character-whitelist regex like [^-+0-9.eE] and new data includes values like 1/5 or 5.000,00 it will not warn you before simply interpreting it as 15 and 5.00