I have a column in an Access table with data that is a string of HTML text, and within the text are numeric data points that I want to separate out, like "Number of Customers", "Sales", and "Profit".
For example, one item in the field sales_description could be "The Number of Customers on 11/27/2020 was 99 with Sales: 967 and Profit: 190"
So far, I've been using the REPLACE function in SQL to remove common characters that I don't want. Now, I want to see if I can use TRIM and SPLIT to get three columns for # Customers, Sales, and Profit.
For this example, the query to pull everything from the table and remove some HTML text is:
SELECT order_id, order_date, REPLACE(REPLACE(sales_description, '', ''), '', '') FROM sales_data;
I want to write a query where the results to have the following fields and value types:
order_id (ex: 0143823)
order_date (ex: 11/27/2020)
num_customers (ex: 35)
sales (ex: 280)
profit (ex: 56)
Thank you for any help you can lend, and let me know if I should provide more details.
There is no ID to extract, but the Number, Sales, and Profit are pretty easy to pull using VBA:
? Val(Split(s, "Sales: ")(1))
967
? Val(Split(s, "Profit: ")(1))
190
? Val(Split(s, " was ")(1))
99
The date takes a little more:
? CDate(Split(Split(s, "Number of Customers on")(1), " ")(1))
2020-11-27
You can write a wrapper function to call these from SQL to extract the values.