Extracting Numeric Data from a Field Using SQL

user_14539490 0
Viewed 44

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.

ms-access trim extract sql split
1 Answers

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.

user_35272970
Related