MySQL搜索"$" (美元符号)失败? [英] MySQL search for "$" (dollar sign) fails?

问题描述

Been要求在大量文本中查找美元价值.可以对美元符号进行搜索吗?如果是这样，将不胜感激任何示例/指导.当前查询...

Been asked to find dollar values across a large body of text. Can a search be performed on the dollar sign? If so, any examples/guidance would be most appreciated. Current query...

select * from concept where concept_description like '%$%';

推荐答案

给出的查询将选择concept_description包含$的行，但是我假设您实际上想提取美元金额吗?如果字段中只有一美元，则可以使用

The queries given will select the rows where concept_description contains a $, but I assume that you want to actually pull out the dollar amounts? If there's only ever just one dollar amount in a field it can be pulled out using

SELECT
SUBSTRING(
    concept_description,
    LOCATE('$', concept_description),
    LOCATE(' ', concept_description, LOCATE('$', concept_description)) - LOCATE('$', concept_description)
)
FROM table
WHERE LOCATE('$', concept_description) > 0

这假设美元金额始终后面有一个空格，并且可能需要对索引进行一些修改.最好是通过一个简单的查询提取整个字段，然后使用正则表达式来获取任何美元值.

This assume that the dollar amount is always followed by a space, and might need some fudging on the indexes. It's probably best to pull the full field out with a simple query, then use regular expressions to grab any dollar values.

这篇关于MySQL搜索"$" (美元符号)失败?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！