Querying Price Ranges in MySQL Without Splitting the Column

When designing a restaurant database, it's common to store a price range—for example, "$8-$4000"—as a single string. It’s simple, user-friendly, and works well for display. However, this structure poses a challenge when you need to filter or sort based on the minimum or maximum price.

Instead of creating separate columns for the lowest and highest price, we can extract and process these values on-the-fly directly from the price_range column using MySQL string functions.

🎯 Use Case

You want to query something like:

• Restaurants with a minimum price of at least $10
• Restaurants with a maximum price of no more than $100
• Sort by lowest price first

But your price_range column contains strings like:

$8-$4000
$12 - $80
$50 - $300

🧩 Solution Using MySQL String Functions

Here's a sample SQL query that extracts the numeric values from price_range for filtering and sorting:

SELECT
    *,
    CAST(SUBSTRING_INDEX(REPLACE(REPLACE(price_range, ' ', ''), '$', ''), '-', 1) AS UNSIGNED) AS lowest_price,
    CAST(SUBSTRING_INDEX(REPLACE(REPLACE(price_range, ' ', ''), '$', ''), '-', -1) AS UNSIGNED) AS highest_price
FROM
    restaurants
ORDER BY
    lowest_price ASC,
    highest_price DESC;

🔍 How It Works

• REPLACE(price_range, ' ', ''): Removes spaces
• REPLACE(..., '$', ''): Removes dollar signs
• SUBSTRING_INDEX(..., '-', 1): Extracts the left part (lowest price)
• SUBSTRING_INDEX(..., '-', -1): Extracts the right part (highest price)
• CAST(... AS UNSIGNED): Converts extracted strings into numeric values

This lets you filter, sort, or group based on prices without altering your schema.

🎛 Filtering by Price Range

Need to query only restaurants with prices between $10 and $100?

SELECT
    *,
    CAST(SUBSTRING_INDEX(REPLACE(REPLACE(price_range, ' ', ''), '$', ''), '-', 1) AS UNSIGNED) AS lowest_price,
    CAST(SUBSTRING_INDEX(REPLACE(REPLACE(price_range, ' ', ''), '$', ''), '-', -1) AS UNSIGNED) AS highest_price
FROM
    restaurants
WHERE
    CAST(SUBSTRING_INDEX(REPLACE(REPLACE(price_range, ' ', ''), '$', ''), '-', 1) AS UNSIGNED) >= 10
    AND CAST(SUBSTRING_INDEX(REPLACE(REPLACE(price_range, ' ', ''), '$', ''), '-', -1) AS UNSIGNED) <= 100;

🧠 Considerations & Best Practices

Here are a few things to keep in mind:

⚠ Performance

These functions are computed at query time, which means they can’t use indexes. If you expect to run many queries based on price, consider:

• Storing min_price and max_price as separate columns (even if hidden from UI)
• Using generated columns with indexing (available in MySQL 5.7+)

📦 Consistency

Make sure all values in price_range follow a standard format. Mixed formats like:

$8-$40
USD 10 - 1000
€15-€100

…will break the logic unless you normalize them first.

✅ Validations

Enforce formatting rules at the application layer or with CHECK constraints (MySQL 8+) to avoid bad data like:

"free"
"tbd"
"$abc-$xyz"

🔄 Optional: Use a View

To simplify future queries:

CREATE VIEW restaurants_with_prices AS
SELECT
    *,
    CAST(SUBSTRING_INDEX(REPLACE(REPLACE(price_range, ' ', ''), '$', ''), '-', 1) AS UNSIGNED) AS lowest_price,
    CAST(SUBSTRING_INDEX(REPLACE(REPLACE(price_range, ' ', ''), '$', ''), '-', -1) AS UNSIGNED) AS highest_price
FROM
    restaurants;

Then you can just:

SELECT * FROM restaurants_with_prices WHERE lowest_price >= 10;

✅ Finally

Storing price ranges as a single string is great for UI and simplicity. But when business logic or analytics needs creep in, parsing the values with SQL functions offers a practical workaround—without altering your schema.

For better performance or stricter control, you may still consider splitting the data in the long run. But for most use cases, this approach is clean, quick, and reliable.

Support Us