Menu
I've seen information on how to add commas to a number in BigQuery, but I am getting dollar amount results
$15,000
$25,000
$10,000
that I would like to convert into pure numbers
15000
25000
10000
I haven't found any BigQuery function for a format change like this.
909
To replace special characters, you can use regular expressions like this [^a-zA-Z0-9]+ and REGEXP_REPLACE function.
Using Regular Expression:select to_number(regexp_replace('Ph: +91 984-809-8540', '\D', '')) OUT_PUT from dual; In this statement '\D' would find all Non-digit characters and the will be replaced by null.
If you want to remove a specific character from your String then you can use the Trimming function to do so. Based on the position of the character that you wish to remove there are three kinds of BigQuery String Functions: TRIM (value1[, value2]): It removes all the leading and trailing characters that match value2.
And here is how it looks in BigQuery after the import. To get the substring between two characters, we will use the REGEXP_SUBSTR() function. This function returns the value of the substring that matches the regular expression, or NULL if no match is found.
many different ways - for example below one uses regular expression to remove $ and , chars
#standardSQL
WITH `project.dataset.table` AS (
SELECT '$15,000' AS dollar_amount UNION ALL
SELECT '$25,000' UNION ALL
SELECT '$10,000'
)
SELECT
dollar_amount,
CAST(REGEXP_REPLACE(dollar_amount, r'\$|,', '') as INT64) amount
FROM `project.dataset.table`
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
