CODEWITHSUNDEEP
mysqlphpmyadmin
Gregg
Gregg

Reputation: 495

How to parse specific number from mySQL text fields

I have a table column with a bunch of text in each field. There is a specific sentence within each field that remains consistent (except for the actual price) that states:

"The price is $15,350."

I would like to parse out the dollar figure from each field and put it into its own column.

What is the SQL statement to make this happen?

Upvotes: 1

Views: 293

Answers (2)

Paul Sasik
Paul Sasik

Reputation: 81479

Try this: (also check out the SQL Fiddle of the solution)

SELECT 
  REPLACE(
    SUBSTRING(
      SUBSTRING( myval,
      LOCATE('The price is $', myval) + 15,
      LENGTH(myval) - LOCATE('The price is $', myval)
    ), 1,
      LOCATE('.',
        SUBSTRING( myval,
          LOCATE('The price is $', myval) + 15,
          LENGTH(myval) - LOCATE('The price is $', myval))
       ) 
    ), ',', '') as Result

FROM tbl

ORIGINAL ANSWER (kept since it covers some basic parsing concepts and also informs the last-submitted solution):

If things are that consistent then a simple series of REPLACE functions to remove text around the original value with an empty strings should do it. Example:

SELECT your_column AS original_value,
REPLACE(REPLACE(your_column, 'The price is $', ''), '$', '') AS parsed_value
FROM your_table

OUTPUT:

original_value           parsed_value
The price is $15,350.    15,350.

You could wrap an additional REPLACE call to remove the comma ',' like this:

SELECT your_column AS original_value,
REPLACE(
    REPLACE(
        REPLACE(your_column, 
            'The price is $', ''), '$', ''), ',', '') AS parsed_value
FROM your_table

NEW OUTPUT:

original_value           parsed_value
The price is $15,350.    15350.

EDIT - Addressing additional text around "The price is..." block:

SELECT your_column AS original_value,
SUBSTRING('The price is $',
    LOCATE ('The price is $', your_column)
REPLACE(
    REPLACE(
        REPLACE(your_column, 
            'The price is $', ''), '$', ''), ',', '')
,

AS parsed_value FROM your_table

The resulting value, 15350., should now easily cast to a decimal with an additional CAST(xxxxx AS DECIMAL(10,2)) wrapped around the result.

Upvotes: 1

Michael Y.
Michael Y.

Reputation: 661

mysql> select replace(replace(mid("The price is $15,350.", 15), ",", ""), ".", "");
+----------------------------------------------------------------------+
| replace(replace(mid("The price is $15,350.", 15), ",", ""), ".", "") |
+----------------------------------------------------------------------+
| 15350                                                                |
+----------------------------------------------------------------------+

Upvotes: 1

Related Questions