SQL查询问题排查：美元格式预算转换无结果或返回空值

问题背景 #

示例数据表 #

预算表

| MOVIE_ID | BUDGET |
|----------+-------------|
| 1904269 | $850,000 |
| 1809508 | NLG 800,000 |
| 1988471 | $40,000 |
| 2119404 | $3,266 |
| 2117105 | $125,000 |
| 2167227 | CAD 280,000 |

票房表

| MOVIE_ID | GROSS |
|----------+------------------------|
| 2463072 | ESP 20,040,964 (Spain) |
| 2044720 | ESP 7,494,043 (Spain) |
| 2083304 | ESP 53,463,024 (Spain) |
| 2461323 | ESP 15,670,733 (Spain) |
| 2318432 | ESP 16,530,040 (Spain) |
| 1874413 | SEK 512,112 (Sweden) |

需求与现有视图 #

需求：

• 创建budget_table视图，仅保留美元格式（$ XXXXXX）的电影ID与预算，预算需转为数值；
• 创建gross_table视图，保留电影ID与最高票房收入；
• 通过票房减预算计算最盈利电影。

现有基础视图：

-- 预算基础视图
CREATE OR REPLACE VIEW budget_table AS 
SELECT movie_id, info AS budget 
FROM movie_info WHERE info_type_id = 105;

-- 票房基础视图
CREATE OR REPLACE VIEW gross_table AS 
SELECT movie_id, info AS gross 
FROM movie_info WHERE info_type_id = 107;

问题SQL与现象 #

为筛选美元预算并转换数值，编写的SQL：

CREATE OR REPLACE VIEW budget_table AS
SELECT movie_id, TO_NUMBER(REPLACE(REGEXP_SUBSTR(info, '\$[0-9]+'), '\$', ''))
                       AS budget
                      FROM movie_info
                      WHERE info_type_id = 105
                      AND REGEXP_LIKE(info, '\$[0-9]+');

现象：

• 执行SELECT * FROM budget_table LIMIT 10;无结果返回；
• 移除REGEXP_LIKE条件后，仅返回movie_id，budget字段全为NULL。

问题原因 #

你的正则表达式有两个关键问题：

1. 未匹配带逗号的金额：示例里的美元预算都带千分位逗号（比如$850,000），但你用的\$[0-9]+只能匹配$后跟连续数字，抓不到带逗号的部分，导致REGEXP_SUBSTR提取不到内容，TO_NUMBER转换后得到NULL；
2. 筛选条件失效：因为正则表达式匹配不上实际的美元格式数据，REGEXP_LIKE会把所有符合要求的美元预算记录都过滤掉，所以查询无结果。

修正后的SQL #

调整正则表达式，支持匹配带逗号的美元金额，同时处理逗号后再转换为数值：

CREATE OR REPLACE VIEW budget_table AS
SELECT 
    movie_id,
    TO_NUMBER(REPLACE(REGEXP_SUBSTR(info, '\$[0-9,]+'), '[$,]', '')) AS budget
FROM movie_info
WHERE 
    info_type_id = 105
    AND REGEXP_LIKE(info, '\$[0-9,]+');

修正点说明 #

• 正则表达式\$[0-9,]+：匹配$开头，后跟数字和逗号的组合，覆盖带千分位的美元格式；
• REPLACE(..., '[$,]', '')：同时去掉$和逗号，得到纯数字字符串，确保TO_NUMBER能正常转换；
• REGEXP_LIKE条件同步调整为\$[0-9,]+，确保能筛选出所有美元格式的预算记录。

验证效果 #

执行修正后的视图创建语句后，再查询：

SELECT * FROM budget_table LIMIT 10;

会返回符合要求的电影ID和数值型的预算字段，示例结果：

内容的提问来源于stack exchange，提问作者hanamontana